How Can I Correctly Update a Specific Number of Rows in MySQL Using LIMIT?

MySQL - UPDATE Query with LIMIT

This article addresses the challenges encountered when attempting to update specific rows through a LIMIT clause in a MySQL database. The goal is to update rows within a specified range of row IDs.

Syntax Error and Correct Query

The query you provided, "UPDATE oltp_db.users SET p_id = 3 LIMIT 1001, 1000", is syntactically incorrect. The correct syntax for using LIMIT in an UPDATE query is to specify a starting row number (offset) and a number of rows to update. The correct query should be:

UPDATE `oltp_db`.`users` SET p_id = 3 LIMIT 1001, 1000;

Updating Rows with NULL Values

Your query, "UPDATE oltp_db.users SET p_id = 3 WHERE p_id = null", is also incorrect. The equality operator "=" cannot be used with the NULL value. To update rows with NULL values, use the IS NULL operator as follows:

UPDATE `oltp_db`.`users` SET p_id = 3 WHERE p_id IS NULL;

Updating Multiple Rows Using LIMIT

To limit the number of rows updated, use a subquery to select a specific range of row IDs, as demonstrated in the following query:

UPDATE table_name SET name='test'
WHERE id IN (
    SELECT id FROM (
        SELECT id FROM table_name 
        ORDER BY id ASC  
        LIMIT 0, 10
    ) tmp
)

This query updates rows with the 'name' column set to 'test' where the row IDs fall within the specified range. The inner subquery selects the first 10 row IDs from the table, which are then used in the IN clause of the outer update query.

The above is the detailed content of How Can I Correctly Update a Specific Number of Rows in MySQL Using LIMIT?. For more information, please follow other related articles on the PHP Chinese website!