MySQL LIMIT on DELETE statement

Question

simply use

DELETE FROM test WHERE 1= 1 LIMIT 10 

the delete query only allows for modifiers after the DELETE 'command' to tell the database what/how do handle things.

see this page

DELETE t.* FROM test t WHERE t.name = 'foo' LIMIT 1

@Andre If I understood what you are asking, I think the only thing missing is the t.* before FROM.

From the documentation:

You cannot use ORDER BY or LIMIT in a multiple-table DELETE.

Use row_count - your_desired_offset

So if we had 10 rows and want to offset 3

 10 - 3 = 7

Now the query delete from table where this = that order asc limit 7 keeps the last 3, and order desc to keep the first 3:

$row_count - $offset = $limit

Delete from table where entry = criteria order by ts asc limit $limit

There is a workaround to solve this problem by using a derived table.

DELETE t1 FROM test t1 JOIN (SELECT t.id FROM test LIMIT 1) t2 ON t1.id = t2.id

Because the LIMIT is inside the derived table the join will match only 1 row and thus the query will delete only this row.