如何在子查詢中使用 LIMIT 根據最新 15 筆記錄刪除 MySQL 中的資料列？

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.

以上是如何在子查詢中使用 LIMIT 根據最新 15 筆記錄刪除 MySQL 中的資料列？的詳細內容。更多資訊請關注PHP中文網其他相關文章！