Mysql delete statement with limit

by

You cannot specify offset in DELETE‘s LIMIT clause.

So the only way to do that is to rewrite your query to something like:

DELETE FROM `chat_messages` 
WHERE `id` IN (
    SELECT `id` FROM (
        SELECT `id` FROM `chat_messages`
        ORDER BY `timestamp` DESC
        LIMIT 20, 50
    ) AS `x`
)

Supposing that you have primary key id column

UPD: You need to implement double nesting to fool mysql, since it doesn’t allow to select from currently modified table (thanks to Martin Smith)

Leave a Comment

tech