Mysql delete statement with limit

mysql sql-order-by limit sql-delete
SBSTP picture SBSTP · Aug 22, 2011 · Viewed 24.5k times · Source

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.

Answer

zerkms picture zerkms · Aug 22, 2011

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)

Related questions

Why does MYSQL higher LIMIT offset slow the query down?

Scenario in short: A table with more than 16 million records [2GB in size]. The higher LIMIT offset with SELECT, the slower the query becomes, when using ORDER BY *primary_key* So SELECT * FROM large ORDER BY `id` LIMIT 0, 30 takes far …

Read More
PHP and MySQL: Order by most recent date and limit 10

I am building a notes system on my site and I've got to the stage where users can post notes into the MySQL database using PHP and then PHP prints them out on a page. However, when they print/echo …

Read More
Is MySQL LIMIT applied before or after ORDER BY?

Which one comes first when MySQL processes the query? An example: SELECT pageRegions FROM pageRegions WHERE(pageID=?) AND(published=true) AND (publishedOn<=?) ORDER BY publishedON DESC LIMIT 1'; Will that return the last published pageRegion even if the record …

Read More