By SBSTP


2011-08-22 00:25:02 8 Comments

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.

1 comments

@zerkms 2011-08-22 00:28:52

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

@Martin Smith 2011-08-22 00:30:28

MySQL doesn't allow that (Error 1093) unless you add an extra level of nesting. See stackoverflow.com/q/3271396/73226

@Jason McCreary 2011-08-22 00:30:42

+1 beating me to the answer - DELETE accepts rowcount only for LIMIT.

@SBSTP 2011-08-22 00:31:02

I dont, but I'm gonna to do that :P. Thanks man

@zerkms 2011-08-22 00:32:12

@Martin Smith: what do you mean?

@Martin Smith 2011-08-22 00:33:37

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.

@zerkms 2011-08-22 00:36:56

@Martin Smith: oh yes, great notice. thank you

Related Questions

Sponsored Content

34 Answered Questions

[SOLVED] Reference - What does this error mean in PHP?

8 Answered Questions

[SOLVED] Adding multiple columns AFTER a specific column in MySQL

  • 2013-07-09 06:22:00
  • Koala
  • 501469 View
  • 325 Score
  • 8 Answer
  • Tags:   mysql ddl

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

7 Answered Questions

[SOLVED] How do I modify a MySQL column to allow NULL?

  • 2008-10-17 16:52:23
  • zmf
  • 354137 View
  • 368 Score
  • 7 Answer
  • Tags:   mysql syntax

3 Answered Questions

[SOLVED] How to get the max of two values in MySQL?

  • 2009-10-14 11:25:39
  • Mask
  • 108875 View
  • 270 Score
  • 3 Answer
  • Tags:   mysql max

6 Answered Questions

[SOLVED] MySQL LIMIT on DELETE statement

5 Answered Questions

[SOLVED] Deleting rows with MySQL LEFT JOIN

10 Answered Questions

[SOLVED] How to remove constraints from my MySQL table?

2 Answered Questions

[SOLVED] Cast from VARCHAR to INT - MySQL

  • 2012-08-26 01:26:30
  • Lenin Raj Rajasekaran
  • 555498 View
  • 235 Score
  • 2 Answer
  • Tags:   mysql sql

6 Answered Questions

[SOLVED] How to delete from multiple tables in MySQL?

Sponsored Content