LyonL LyonL - 3 months ago 20
MySQL Question

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

Answer

Do you try this one?

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