How to Efficiently Delete All but the N Most Recent Records in MySQL?

MySQL: Removing All But the N Most Recent Entries

Database management often requires removing older records while preserving the most recent ones. This can be achieved in MySQL using a clever SQL query without relying on variables.

A common, but flawed, attempt is:

DELETE FROM table ORDER BY id ASC LIMIT ((SELECT COUNT(*) FROM table) - N);

This approach fails due to limitations in MySQL's DELETE statement.

A robust solution involves a nested subquery:

DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT 42 -- Replace 42 with the number of records to keep (N)
  ) as recent_records
);

The nested subquery is crucial. Without it, you'll encounter errors like:

• SQL Error (1093): You can't modify a table you're selecting from within the same query.
• SQL Error (1235): Using LIMIT directly within a NOT IN subquery is prohibited.

This structured query efficiently bypasses these restrictions, successfully deleting all but the specified number of recent records (defined by the LIMIT clause). Remember to replace 42 with your desired value of N.

The above is the detailed content of How to Efficiently Delete All but the N Most Recent Records in MySQL?. For more information, please follow other related articles on the PHP Chinese website!