MySQL DELETE all but latest X records

Question

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) 

Retention

Can you add a timestamp field to your table? – Grumpy

If your ID column is an identity column with autoincrement of 1, you could do somthing like that: DELETE FROM chat WHERE ID NOT IN(SELECT TOP 50 ID FROM chat ORDER BY ID DESC) – Superannuation

In this case use: DELETE FROM chat WHERE datecol NOT IN(SELECT TOP 50 datecol FROM chat ORDER BY datecol DESC)-- SQL Server DELETE FROM chat WHERE datecol NOT IN(SELECT datecol FROM chat ORDER BY datecol DESC LIMIT 50) --MySQL – Superannuation

@Retention - I think you should delete based on a time span (between x and x), rather than the number of records. Is there some reason why it has to be the last 50, rather than a time period/span? – Grumpy

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – Retention

possible duplicate of SQL query: Delete all records from the table except latest N? – Rhythmics

Answer 1

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.

Walachia

Better order those IDs in DESCending order. – Aardvark

Tried this it doesnt work :/ I have a date field too if that helps – Retention

Whoops. Your question is tagged with SQL - try now using the MySQL solution. – Walachia

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – Retention

It is now edited to avoid the subquery LIMIT problem - try now, it works. – Walachia

The 2nd query is not for SQL in general. It only works in SQL-Server (and Ms-Access). – Swung

This works but I don't understand why I need the 'x' there. Anyone care to explain? – Latif

@Latif this comment is old but since I had the same question, here seems a good place to share the explanation I found: https://mcmap.net/q/41320/-mysql-error-1093-can-39-t-specify-target-table-for-update-in-from-clause – Steger

You are the master! :) – Tensity

Answer 2

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

Arbor

Answer 3

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

Eyeless

What database system do you use? – Eyeless