How to Delete All Posts Except the Latest 15 in MySQL Without 'LIMIT & IN' Subquery Support?

MySQL Subquery Limit: Deleting Posts Excluding Latest 15

In the realm of database optimization, it's often necessary to remove redundant or outdated data. This question explores how to delete all posts except the latest 15 using a MySQL subquery.

The original query:

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

However, this query encounters an error due to MySQL's lack of support for 'LIMIT & IN/ALL/ANY/SOME' subqueries in its earlier versions. To address this issue, a modified query can be employed:

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

In this modified query, an additional subquery is introduced as a derived table using the alias 't'. This derived table contains the IDs of the latest 15 posts, sorted in descending order based on their timestamp. The main query then utilizes this derived table to exclude those specific IDs when deleting posts.

By using this workaround, it's possible to achieve the desired result of deleting all posts except the most recent 15 in MySQL versions that don't support 'LIMIT & IN/ALL/ANY/SOME' subqueries. This revised approach effectively removes obsolete posts while preserving the latest ones for further reference.

The above is the detailed content of How to Delete All Posts Except the Latest 15 in MySQL Without 'LIMIT & IN' Subquery Support?. For more information, please follow other related articles on the PHP Chinese website!