How to Delete All But the Latest N Records in a MySQL Table Using SQL?

Efficiently Removing Older MySQL Records: Keeping Only the Latest N

MySQL doesn't directly support deleting records with a LIMIT clause in a subquery. However, we can achieve this using nested subqueries for a clean solution.

Let's say you need to remove all but the most recent N entries, ordered by the id column (descending). This approach avoids the common pitfall of using LIMIT directly in the DELETE statement:

The following query effectively achieves this:

DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT N
  ) as latest_records
);

This query leverages a subquery (latest_records) to identify the IDs of the N most recent records. The outer DELETE statement then removes any rows whose IDs are not in this set.

Remember that performance can be further optimized depending on your specific table structure and data volume. Consider adding indexes to the id column for significant performance improvements.

The above is the detailed content of How to Delete All But the Latest N Records in a MySQL Table Using SQL?. For more information, please follow other related articles on the PHP Chinese website!