Skip to content
Advertisement

MYSQL delete all rows, but the same query on select return only 3 rows

I can’t understand why the same query for select and delete have different behavior.
I need to delete all rows except 5 newest rows.
I know my solution for this task is no good, but my question is about why MySQL no delete the same rows, that return select for the same query clause

see code

now I need delete rows with id 1,2,3

Now I use delete operation

I cry;

Advertisement

Answer

We can try doing a delete limit join here:

The idea behind this anti-join is that we will delete any record which does not match to one of the first five records, as ordered descending by the created_at column.

Note that we can’t use a WHERE IN query here, because MySQL will return the dreaded error message that LIMIT is not yet supported in this version.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement