SQL query: Delete all records from the table except latest N?

by

You cannot delete the records that way, the main issue being that you cannot use a subquery to specify the value of a LIMIT clause.

This works (tested in MySQL 5.0.67):

DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT 42 -- keep this many records
  ) foo
);

The intermediate subquery is required. Without it we’d run into two errors:

  1. SQL Error (1093): You can’t specify target table ‘table’ for update in FROM clause – MySQL doesn’t allow you to refer to the table you are deleting from within a direct subquery.
  2. SQL Error (1235): This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’ – You can’t use the LIMIT clause within a direct subquery of a NOT IN operator.

Fortunately, using an intermediate subquery allows us to bypass both of these limitations.

Nicole has pointed out this query can be optimised significantly for certain use cases (such as this one). I recommend reading that answer as well to see if it fits yours.

More Related Contents:

  1. Remove duplicate rows in MySQL
  2. How to delete from multiple tables in MySQL?
  3. MySQL DELETE FROM with subquery as condition
  4. SQL DELETE with JOIN another table for WHERE condition
  5. How do I delete all the duplicate records in a MySQL table without temp tables
  6. Deleting a row based on the max value
  7. How can I return pivot table output in MySQL?
  8. Using LIMIT within GROUP BY to get N results per group?
  9. How to perform grouped ranking in MySQL
  10. Required to join 2 tables with their FKs in a 3rd table
  11. Does COUNT(*) always return a result?
  12. Ordering by specific field value first
  13. How to change the CHARACTER SET (and COLLATION) throughout a database?
  14. SQL: Creating a Relational table with 2 different auto_increment
  15. The total number of locks exceeds the lock table size
  16. MySQL variable format for a “NOT IN” list of values
  17. Convert partially non-numeric text into number in MySQL query
  18. MySQL “IN” operator performance on (large?) number of values
  19. Order by COUNT per value
  20. MySQL Select last 7 days
  21. Second SELECT query if first SELECT returns 0 rows
  22. Difference between two table structure
  23. Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)
  24. How to retrieve JSON data from MySQL?
  25. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  26. Why MySQL is giving error “Not allowed to return a result set from a function”?
  27. Delete from one table with join
  28. When to use a left outer join?
  29. MySQL: Computed Column
  30. mySQL query to search all tables within a database for a string?

Leave a Comment