How to Delete Records Based on a Specific Criteria in MySQL using Subqueries?

Using Subqueries for Data Manipulation in MySQL

The task at hand involves selectively deleting records from a table based on a specific criteria. By leveraging subqueries, we can accomplish this using MySQL.

To begin, let's consider the following query:

DELETE FROM posts WHERE id NOT IN
(SELECT id FROM posts ORDER BY timestamp DESC LIMIT 0, 15)

This query aims to delete posts that are not among the latest 15 records, as ordered by the "timestamp" field. However, MySQL versions prior to 8.0 encounter an error when attempting to combine a LIMIT clause with an IN subquery.

To overcome this limitation, we can employ a nested subquery workaround. The revised query becomes:

DELETE 
FROM posts 
WHERE id NOT IN (
      SELECT * FROM (
            SELECT id 
            FROM posts 
            ORDER BY timestamp DESC LIMIT 0, 15
      ) 
      AS t);

In this rewritten query, we create an inner subquery to fetch the IDs of the latest 15 posts. The outer subquery then aliasing the results using the "AS t" clause.

By using this nested subquery approach, we can effectively achieve the desired result, even with older MySQL versions that lack full support for LIMIT and IN combination.

The above is the detailed content of How to Delete Records Based on a Specific Criteria in MySQL using Subqueries?. For more information, please follow other related articles on the PHP Chinese website!