CODEWITHSUNDEEP
mysql
LyonL
LyonL

Reputation: 83

Mysql, delete several rows each N rows

I explain the problem, and the solution I tried to implement. I have a table with a lot of data, and I want to delete 4 rows each 5 rows. The aim is to have a lighter table.

This is my request :

SET @var_name = -1;
DELETE FROM myTable
   WHERE id IN
   (
   SELECT id FROM myTable HAVING (@var_name := @var_name +1) % 5 !=0
   )

The SELECT operation works properly, but together with the DELETE operation I get this message

#1093 - Table 'myTable' is specified twice, both as a target for 'DELETE' and as a separate source for data

I understand the meaning : I can't delete the table as it in the request. A workaround is possible : get the full list in a console, and execute the DELETE operation. It's better to perform it in one line.

Thanks for you help. Thanks a lot

Upvotes: 0

Views: 60

Answers (1)

Marcus
Marcus

Reputation: 1930

Do you try this one?

SET @var_name = -1;
DELETE FROM myTable WHERE (@var_name := @var_name +1) % 5 = 0

Upvotes: 1

Related Questions