Search code examples
mysqlsql-deleterecords

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)
Solution

  • 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.