How to Update a Specific Range of Rows in MySQL and Handle NULL Values?

MySQL: Updating Rows with LIMIT

Updating a specific range of rows in MySQL can be achieved using a subquery in conjunction with your UPDATE statement. The syntax error you encountered in your initial query stems from incorrectly specifying the limit clause.

To update rows from 1001 to the next 1000, use the following construct:

UPDATE table_name
SET p_id = 3
WHERE id IN (
    SELECT id
    FROM (
        SELECT id
        FROM table_name
        ORDER BY id ASC
        LIMIT 1000, 1000
    ) tmp
)

Regarding the issue with updating NULL values, your query:

UPDATE table_name SET p_id = 3 WHERE p_id = null

is not correct because MySQL treats NULL as a distinct value. To update NULL values, use the IS NULL comparison operator:

UPDATE table_name SET p_id = 3 WHERE p_id IS NULL

The above is the detailed content of How to Update a Specific Range of Rows in MySQL and Handle NULL Values?. For more information, please follow other related articles on the PHP Chinese website!