How to Delete Posts Outside the Latest 15 in MySQL 5.5.8?

mySQL Subquery Limit: Resolving 'This version of MySQL doesn't support 'LIMIT & IN/ALL/ANY/SOME subquery' Error

The provided query seeks to delete posts not included in the latest 15 entries, but it encounters the error "MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery.'" This is due to limitations in MySQL version 5.5.8.

Proposed Solution:

To overcome this limitation, employ the following modified query:

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

This query uses a nested SELECT statement to create a subquery that retrieves the IDs of the latest 15 posts. The main query then utilizes this subquery to identify and delete the posts that fall outside this range.

Explanation:

Internally, the modified query follows a two-step process:

1. It creates a temporary table, represented by the subquery alias 't', which contains the IDs of the latest 15 posts.
2. The main query then uses this temporary table to filter out the posts that do not have their IDs present in the subquery, thereby achieving the desired deletion.

The above is the detailed content of How to Delete Posts Outside the Latest 15 in MySQL 5.5.8?. For more information, please follow other related articles on the PHP Chinese website!