How to Delete All but the Latest N Records in MySQL Using a Single Query?

Use SQL query to delete all records except the latest N records

In MySQL, it is possible to delete all records in a table except the latest N records sorted by descending ID. This can be achieved using a single MySQL query, although not as straightforward as expected.

Simply use a query like this:

<code class="language-sql">delete from table order by id ASC limit ((select count(*) from table ) - N)</code>

will fail because the value of the LIMIT clause cannot be specified using a subquery. To achieve this a more complex query is required:

<code class="language-sql">DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT N
  ) foo
);</code>

In this query, use an intermediate subquery to select the IDs of the latest N records. The main query then uses this subquery in a NOT IN operator to exclude these records from the deletion process. This approach allows us to bypass the limitations of using subqueries directly in the LIMIT clause or in the FROM clause of the main query.

It is worth noting that the intermediate subquery is essential. Without it, we would encounter errors related to referencing the table being dropped in a subquery and limitations imposed by earlier versions of MySQL that did not support the use of a LIMIT clause in a subquery used in the NOT IN operator.

By using this query, you can effectively delete all records in the table except the latest N records sorted by a specific column, ensuring that the latest data is retained.

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