How to Delete a Specific Number of Rows in Descending Order in MySQL with Restrictions?

Deleting Rows with Restrictions in MySQL

This question explores the usage of the DELETE statement with the LIMIT clause in MySQL. The user attempts to delete a specified number of rows in descending order by the timestamp column. However, the query fails with a syntax error.

The error arises because MySQL does not permit offsets in the LIMIT clause of a DELETE statement. Consequently, the query cannot be executed as intended.

To resolve this issue, the query can be rewritten using a different approach. The rewritten query employs nested subqueries to select the rows to be deleted based on the desired range.

DELETE FROM `chat_messages` 
WHERE `id` IN (
    SELECT `id` FROM (
        SELECT `id` FROM `chat_messages`
        ORDER BY `timestamp` DESC
        LIMIT 20, 50
    ) AS `x`
)

In this modified query, a nested subquery selects the id values of the rows to be deleted. The LIMIT clause is applied to the innermost subquery to restrict the result set.

Since MySQL prohibits selecting from a currently modified table, the query uses double nesting to work around this limitation. The outer subquery serves as a temporary table, allowing the innermost subquery to select id values from a separate result set.

By implementing this technique, the query can successfully delete rows within the specified range while adhering to MySQL's syntax requirements.

The above is the detailed content of How to Delete a Specific Number of Rows in Descending Order in MySQL with Restrictions?. For more information, please follow other related articles on the PHP Chinese website!