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)

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)` — pistipanko, Jan 16 '12 at 20:51
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` — pistipanko, Jan 16 '12 at 20:57
@user1022585 - 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? — O.O, Jan 16 '12 at 21:01
`#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'` — user1022585, Jan 16 '12 at 21:02
possible duplicate of [SQL query: Delete all records from the table except latest N?](http://stackoverflow.com/questions/578867/sql-query-delete-all-records-from-the-table-except-latest-n) — e-sushi, Nov 23 '13 at 03:43

Brissles · Accepted Answer · 2017-01-17T18:05:24.900

19

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.

edited Jan 17 '17 at 18:05

answered Jan 16 '12 at 20:48

Brissles

3,805
22
31

Better order those IDs in DESCending order. – Joe Stefanelli Jan 16 '12 at 20:50
Tried this it doesnt work :/ I have a `date` field too if that helps – user1022585 Jan 16 '12 at 20:54
Whoops. Your question is tagged with SQL - try now using the MySQL solution. – Brissles Jan 16 '12 at 20:56
1

`#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'` – user1022585 Jan 16 '12 at 21:02
It is now edited to avoid the subquery LIMIT problem - try now, it works. – Brissles Jan 16 '12 at 21:04
The 2nd query is not for SQL in general. It only works in SQL-Server (and Ms-Access). – ypercubeᵀᴹ Jan 17 '12 at 00:15
This works but I don't understand why I need the 'x' there. Anyone care to explain? – user1767586 Jun 18 '13 at 14:53
@user1767586 this comment is old but since I had the same question, here seems a good place to share the explanation I found: https://stackoverflow.com/a/45498/3872061 – fpierrat May 18 '18 at 07:48
You are the master! :) – Sasho Apr 05 '20 at 13:24

score 2 · Answer 2 · answered Jun 09 '15 at 12:31

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

Mithrandir · Answer 3 · 2012-01-16T21:08:50.157

0

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

edited Jan 16 '12 at 21:08

answered Jan 16 '12 at 20:52

Mithrandir

23,979
6
45
64

What database system do you use? – Mithrandir Jan 16 '12 at 21:00

MySQL DELETE all but latest X records

3 Answers3