MySQL DELETE With Limit After Offset-CodePudding

Hopefully this is a simple question and I'm overlooking something trivial, but I can't seem to find the answer in quite a bit of searching.

When doing a SELECT query, I can do LIMIT like this:

SELECT * FROM `table` LIMIT 20,20

That retrieves up to 20 rows starting with an offset of 20.

Now, suppose that I want to delete those rows I selected?

One might expect to be able to do this:

DELETE FROM `table` LIMIT 20,20

However, for whatever reason, this isn't part of MySQL syntax.

One solution which works if the table had an id field would be:

DELETE FROM `table` WHERE `id` >= 20 LIMIT 20

However, it requires me to find what the 20th id value is first, and requires that there be an id field. Not all of my tables have id fields. It also will give different results if table rows are in a different order than ascending by id.

I'm trying to find a generic solution because I'm dealing with tables that have a wide variety of different columns, so I can't depend on any particular column to exist. A few of my tables don't even have a primary index.

For my particular application, I'm removing some cells from the end of the table that got copied erroneously, but I'm curious if there's a clever solution to remove from the middle as well.

Anyway, I appreciate any help or ideas! Thanks so much!

CodePudding user response：

You could use a LIMIT subquery as follows:

DELETE
FROM yourTable
WHERE id IN (SELECT id
             FROM (SELECT id FROM yourTable ORDER BY id LIMIT 20) t
);

CodePudding user response：

DELETE t1
FROM `table` t1
NATURAL JOIN ( SELECT * 
               FROM `table` 
               ORDER BY {expression} LIMIT 20,20 ) t2

LIMIT without ORDER BY makes no sense - hence ORDER BY with expression placeholder is added.

DEMO

PS. Of course you may use not NATURAL joining but INNER one with proper joining expression (for example, by primary key, or by sorting expression if it provides rows uniqueness).