vendredi 31 juillet 2015

want to delete oldest record when a new one is added leaving no more then 5 records

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