I have a table in mySQL in which I log last twenty queries done on my website. I want to delete rows after 20th row. I use id but as an Auto_Increment..
3 Answers
try something like this:
delete from <table> where id not in
(select id from <table> order by <datecol> desc limit 20)
Comments
DROP TEMPORARY TABLE IF EXISTS temp_ids;
CREATE TEMPORARY TABLE temp_ids(id INT);
INSERT INTO temp_ids (id)
SELECT id FROM myTable ORDER BY id DESC LIMIT 20;
DELETE FROM myTable
WHERE
id NOT IN (SELECT id FROM temp_ids);
TEMPORARY TABLE because you need to avoid Error Code: 1093. You can't specify target table 'myTable' for update in FROM clause and also This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
Edit
@gaurav.mishra280295 - You add Order By id. Even if id is an autoincremental PK there is at least no 100% guarantee it is also the insert order. Some delay is possible it's not 100% reliable.
Just only as information.
Comments
As the ids are monotonic and increasing by one each time, why not create two other tables. One storing the oldest id. The other storing the number of ids. Then using these two tables you can work out when to start deleting rows and which one to delete. When deleting just increase the id in the table to record the next one to delete.
I would recommend doing this in a stored procedure.
