What is a efficient way to delete the most recent X record from SQL?

Question

I have a simple table where one of the field is a date column. I can select the most recent X records by doing

Select * from MyTable order by last_update desc limit X

But how do I efficiently delete those column? Is subselect the quickest?

Answer 1

You can do "delete from where" in exactly the same way, with the same conditions as the original query. e.g:

DELETE FROM Person WHERE Person.name = "jeff"

or

DELETE FROM Person WHERE Person.joinTime > 12001234567 LIMIT 100

If you want to delete a range (e.g most recent 10) you could try:

DELETE * FROM Person WHERE id >= ( LAST_INSERT_ID() - 10 );

Answer 2

Perhaps something like:

DELETE FROM MyTable WHERE rowid IN
(SELECT rowid FROM MyTable ORDER BY last_update DESC LIMIT X);

Answer 3

If I recall correctly the IN clause accepts a sub select. Could be wrong.

DELETE FROM Person 
WHERE 
  Person.ID IN ( 
     SELECT t.ID 
     FROM 
        Person t 
     ORDER BY 
        t.joinTime DESC
     LIMIT X
   )

Answer 4

DELETE  d.*
FROM    mytable d
LEFT JOIN
        (
        SELECT  id
        FROM    mytable
        ORDER BY
                last_update DESC
        LIMIT   10
        ) q
ON      d.id = q.id
WHERE   q.id IS NULL

If your are using MySQL, this is the preferred solution, since IN (SELECT ... LIMIT) does not work in MySQL.

See this entry in my blog for more details:

• Keeping rows