34

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.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
SBSTP
  • 3,479
  • 6
  • 30
  • 41
  • Take a look at this question also posted on StackOverflow: http://stackoverflow.com/q/6111961/799653 – Drew Aug 22 '11 at 00:28

1 Answers1

54

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)

Mr.Singh
  • 1,421
  • 6
  • 21
  • 46
zerkms
  • 249,484
  • 69
  • 436
  • 539
  • 1
    MySQL doesn't allow that (Error 1093) unless you add an extra level of nesting. See http://stackoverflow.com/q/3271396/73226 – Martin Smith Aug 22 '11 at 00:30
  • +1 beating me to the answer - `DELETE` accepts `rowcount` only for `LIMIT`. – Jason McCreary Aug 22 '11 at 00:30
  • @Martin Smith: what do you mean? – zerkms Aug 22 '11 at 00:32
  • 1
    I edited my comment to add further details. If the subquery is directly in the `IN` you get error "You can't specify target table 'chat_messages' for update in FROM clause". You can get around that by making the subquery a derived table. – Martin Smith Aug 22 '11 at 00:33