CODEWITHSUNDEEP
mysqlsql-delete
user1022593
user1022593

Reputation: 11

DELETE all the old rows except the top 20 new

I want to delete all old rows from "idx_links" table except the 20 newest. pubDate is timestamp.

This select brings the rows:

SELECT @row := @row + 1 AS row, t.*
FROM idx_links t, (SELECT @row := 0) r
having(row>20)
ORDER BY pubDate DESC

How can I delete this rows?

Upvotes: 1

Views: 628

Answers (1)

p.campbell
p.campbell

Reputation: 100567

How about this - delete all rows except the top 20 by pubDate. 

 DELETE idx_links
 WHERE ID NOT IN (SELECT ID FROM idx_links 
                  ORDER BY pubDate DESC LIMIT 0,20);

Alternately by the date, and avoiding a subquery for the IN clause:

DELETE idx_links
WHERE pubDate < (SELECT pubDate FROM idx_links 
                 ORDER BY pubDate DESC LIMIT 20,1);

Upvotes: 3

Related Questions