How to Delete a Specific Range of Rows in MySQL Using LIMIT Clause?

MySQL DELETE Statement with Limit

A user encounters an error while attempting to delete rows from a table using the following query:

DELETE FROM `chat_messages` ORDER BY `timestamp` DESC LIMIT 20, 50;

The error message indicates that the syntax is incorrect.

Explanation

The error occurs because the DELETE statement used here includes an offset in the LIMIT clause. This is not allowed in MySQL. The LIMIT clause can only specify the number of rows to be deleted.

Solution

To delete a specified range of rows from a table, you cannot use the offset feature in the LIMIT clause. Instead, you need to use a subquery to select the rows to be deleted and then delete them using their primary key values.

The following query can be used in this scenario:

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

This query uses double nesting to select the rows to be deleted and work around the limitations of MySQL.

The above is the detailed content of How to Delete a Specific Range of Rows in MySQL Using LIMIT Clause?. For more information, please follow other related articles on the PHP Chinese website!