CODEWITHSUNDEEP
mysql
Gaurav Mishra
Gaurav Mishra

Reputation: 135

How to delete specific numbers of rows in mySQL

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..

Upvotes: 2

Views: 1636

Answers (3)

edze
edze

Reputation: 3005

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.

Upvotes: 1

Ed Heal
Ed Heal

Reputation: 59997

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.

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

try something like this:

delete from <table> where id not in
(select id from <table> order by <datecol> desc limit 20)

Upvotes: 3

Related Questions