How to Use LIMIT in MySQL UPDATE Queries?

Updating Rows with Limit in MySQL

In MySQL, using the LIMIT clause in an UPDATE query can be confusing. Here, we provide a detailed explanation of its usage and address related questions.

Query with LIMIT

To update rows within a specific range, you can use the following syntax:

UPDATE table_name SET column_name = value
WHERE id IN (
    SELECT id FROM (
        SELECT id FROM table_name
        ORDER BY id ASC
        LIMIT start_row, row_count
    ) tmp
)

For example, to update rows from 1001 to 1100, use:

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

Invalid Query

The query you provided (UPDATE oltp_db.users SET p_id = 3 LIMIT 1001, 1000) is invalid because LIMIT cannot be used directly in an UPDATE statement. It must be applied within a subquery as shown above.

Updating Null Values

If the p_id column contains NULL values, the following query will not work:

UPDATE `oltp_db`.`users` SET p_id = 3 WHERE p_id = null

To update NULL values, you can use the IS NULL condition:

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

The above is the detailed content of How to Use LIMIT in MySQL UPDATE Queries?. For more information, please follow other related articles on the PHP Chinese website!