How to Delete Rows in MySQL Based on the Latest 15 Records Using LIMIT in a Subquery?

mySQL Subquery with LIMIT Restriction

In MySQL, attempting to use a LIMIT clause within a subquery that employs the IN operator can trigger an error message stating that MySQL does not support this functionality. This article provides a solution to this issue, allowing you to delete rows from a table based on a specific condition related to the latest 15 records in the table.

To achieve the desired result, you can utilize the following query structure:

DELETE 
FROM posts 
WHERE id not in (
      SELECT * FROM (
            SELECT id 
            FROM posts 
            ORDER BY timestamp desc 
            limit 0, 15
      ) 
      as t);

The outer DELETE statement targets the posts table for row deletion. The IN operator is used to identify the rows that should be retained, which are those found in the subquery result.

The subquery selects the id field from the posts table, orders the results in descending order by timestamp, and applies a LIMIT clause to retrieve the top 15 records. By using the alias t, the subquery creates a temporary table for efficient processing.

This workaround effectively eliminates the MySQL limitation and allows you to perform the intended operation of deleting all posts that do not fall within the most recent 15 records.

The above is the detailed content of How to Delete Rows in MySQL Based on the Latest 15 Records Using LIMIT in a Subquery?. For more information, please follow other related articles on the PHP Chinese website!