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)

More Related Contents:

  1. Why does MYSQL higher LIMIT offset slow the query down?
  2. MySQL LIMIT on DELETE statement
  3. Remove duplicate rows in MySQL
  4. MySQL order by before group by
  5. MySQL ‘Order By’ – sorting alphanumeric correctly
  6. Combining UNION and LIMIT operations in MySQL query
  7. Ordering by specific field value first
  8. MySQL “Group By” and “Order By”
  9. Using union and order by clause in mysql
  10. MySQL DELETE FROM with subquery as condition
  11. GROUP_CONCAT ORDER BY
  12. What is MySQL row order for “SELECT * FROM table_name;”?
  13. Slow query when using ORDER BY
  14. MySQL – Control which row is returned by a group by
  15. MySQL: Sort GROUP_CONCAT values
  16. MySQL order by “best match”
  17. MySQL/MariaDB – order by inside subquery
  18. How can I speed up a MySQL query with a large offset in the LIMIT clause?
  19. MySQL Order before Group by
  20. SQL Order By Count
  21. ORDER BY date and time BEFORE GROUP BY name in mysql
  22. Returning query results in predefined order
  23. How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?
  24. How do I delete all the duplicate records in a MySQL table without temp tables
  25. Sort the rows according to the order specified in WHERE IN clause
  26. Delete from one table with join
  27. UPDATE with ORDER BY and LIMIT not working in MYSQL
  28. Delete all rows with timestamp older than x days
  29. Deleting a row based on the max value
  30. delete rows from multiple tables

Leave a Comment