I have the following table:
id user_id message timestamp
---- ------- -------- ----------
1 abc message1 2015-07-27 19:10:40
2 def message1 2015-07-27 19:20:41
3 xyz message1 2015-07-27 19:30:41
4 abc message2 2015-07-28 19:11:40
5 abc message3 2015-07-28 19:12:40
6 abc message4 2015-07-28 19:13:40
7 abc message5 2015-07-28 19:14:40
When user 'abc' adds the 6th message I want the 1st one (oldest) to be deleted.
I'm trying the following two SQL queries but it only leaves 5 messages total not taking into account the user_id
INSERT INTO sample(user_id, message, created)
VALUES('abc', 'message6', now());
DELETE FROM sample
WHERE user_id = 'abc'
AND id NOT IN (SELECT id
FROM
(SELECT id
FROM sample
ORDER BY id DESC
LIMIT 5) x
);
Thanks in advance
Aucun commentaire:
Enregistrer un commentaire