SQL query: Delete all records from the table except latest N?

Question

Is it possible to build a single mysql query (without variables) to remove all records from the table, except latest N (sorted by id desc)?

Something like this, only it doesn't work :)

delete from table order by id ASC limit ((select count(*) from table ) - N)

Thanks.

Answer 1

DELETE FROM table WHERE ID NOT IN
(SELECT MAX(ID) ID FROM table)

Answer 2

DELETE  i1.*
FROM    items i1
LEFT JOIN
        (
        SELECT  id
        FROM    items ii
        ORDER BY
                id DESC
        LIMIT 20
        ) i2
ON      i1.id = i2.id
WHERE   i2.id IS NULL

Answer 3

If your id is incremental then use something like

delete from table where id < (select max(id) from table)-N

Answer 4

DELETE FROM table WHERE id NOT IN (SELECT id FROM table ORDER BY id, desc LIMIT 0, 10)

Answer 5

Unfortunately for all the answers given by other folks, you can't DELETE and SELECT from a given table in the same query.

DELETE FROM mytable WHERE id NOT IN (SELECT MAX(id) FROM mytable);

ERROR 1093 (HY000): You can't specify target table 'mytable' for update 
in FROM clause

Nor can MySQL support LIMIT in a subquery. These are limitations of MySQL.

DELETE FROM mytable WHERE id NOT IN 
  (SELECT id FROM mytable ORDER BY id DESC LIMIT 1);

ERROR 1235 (42000): This version of MySQL doesn't yet support 
'LIMIT & IN/ALL/ANY/SOME subquery'

The best answer I can come up with is to do this in two stages:

SELECT id FROM mytable ORDER BY id DESC LIMIT n;

Collect the id's and make them into a comma-separated string:

DELETE FROM mytable WHERE id NOT IN ( ...comma-separated string... );

(Normally interpolating a comma-separate list into an SQL statement introduces some risk of SQL injection, but in this case the values are not coming from an untrusted source, they are known to be id values from the database itself.)

note: Though this doesn't get the job done in a single query, sometimes a more simple, get-it-done solution is the most effective.

Answer 6

You cannot delete the records that way.

DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT 42 -- keep this many records
  ) foo
);

Tested, working (MySQL 5.0.67). The intermediate subquery is required.

Answer 7

Why not

DELETE FROM table ORDER BY id DESC LIMIT 1, 123456789

Just delete all but the first row (order is DESC!), using a very very large nummber as second LIMIT-argument. See here

Answer 8

This should work as well:

DELETE FROM [table] INNER JOIN (SELECT [id] FROM (SELECT [id] FROM [table] ORDER BY [id] DESC LIMIT N) AS Temp) AS Temp2 ON [table].[id] = [Temp2].[id]