Search code examples
mysqlsql-order-bylimitsql-delete

Mysql delete statement with limit

I'm trying to delete rows from a table but I get an error.

DELETE FROM `chat_messages` ORDER BY `timestamp` DESC LIMIT 20, 50;

I get this error at 50:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 50' at line 1

No idea what's wrong.

Solution

  • 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)