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

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.

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.

1 Okay that works -- but to me, it's inelegant and unsatisfying to have to resort to arcane tricks like that. +1 nevertheless for the answer. – Bill Karwin Feb 23 '09 at 19:39 1 I mark it as an accepted answer, because it does what I asked for.

But I personally will do it probably in two queries just to keep it simple :) I thought maybe there was some quick and easy way. – serg Feb 23 '09 at 20:53.

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.

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.

You're terribly right :( – Quassnoi Feb 23 '09 at 19:12 But you can do inner joins between a delete and select. What I did below should work. – achinda99 Feb 23 '09 at 19:13 You need to use an intermediary subquery to get LIMIT working in the subquery.

– Alex Barrett Feb 23 '09 at 19:17 @achinda99: I'm not seeing an answer from you on this thread...? – Bill Karwin Feb 23 '09 at 19:55 I got pulled for a meeting. My bad. I don't have a test environment right now to test the sql I wrote, but I've done both what Alex Barret did and I've gotten it to work with an inner join.

– achinda99 Feb 23 '09 at 20:20.

If your id is incremental then use something like delete from table where id.

One big problem in this nice trick: serials are not always contiguous (for instance when there were rollbacks). – bortzmeyer Apr 14 '09 at 9:50.

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.

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

Good, and simpler than Alex Barrett's version. – bortzmeyer Apr 14 '09 at 9:47.

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

This will only leave single latest row – Justin Wignall Feb 23 '09 at 19:04.

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.

What about : SELECT * FROM table del LEFT JOIN table keep ON del. Id N; It returns rows with more than N rows before. Could be useful?

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.