CODEWITHSUNDEEP
mysqlsql-deleterecords
user1022585
user1022585

Reputation: 13641

MySQL DELETE all but latest X records

I have a script that runs every hour on my php site. In that script I would like some kind of MySQL query to delete every record from a table but say the latest 50.

How would I do something like that? 

// pseudo code: like this?
DELETE from chat WHERE id = max (ID - 50)

Upvotes: 11

Views: 9555

Answers (3)

Brissles
Brissles

Reputation: 3881

You could try using NOT IN:

EDIT for MySQL:

DELETE FROM chat WHERE id NOT IN ( 
  SELECT id 
  FROM ( 
    SELECT id 
    FROM chat 
    ORDER BY id DESC 
    LIMIT 50
  ) x 
);

This is for SQL-Server:

DELETE FROM chat WHERE id NOT IN 
    (SELECT TOP 50 id FROM chat ORDER BY id DESC)

Assuming higher values of id are always newer.

Upvotes: 19

esp
esp

Reputation: 7677

NOT IN is inefficient. You can slightly modify the first option in the previous answer by @Mithrandir to make it look like this:

DELETE from chat WHERE id < 
  (SELECT id FROM 
    (SELECT id FROM chat ORDER BY id DESC LIMIT 50) t ORDER BY id ASC LIMIT 1));

Upvotes: 2

Mithrandir
Mithrandir

Reputation: 25337

You could try something like this:

DELETE from chat WHERE id < (SELECT max(ID)-50 FROM chat)

This would work if your values for ID are incremented in steps of 1. Or you can use something like:

DELETE FROM chat WHERE id NOT IN 
    (SELECT id FROM ( SELECT ID FROM chat ORDER BY datetime_col DESC LIMIT 50) t ) -- mysql

Upvotes: 0

Related Questions