Why Does My MySQL CASE WHEN Update Affect All Rows, and How Can I Fix It?

MySQL Update Using CASE WHEN/THEN/ELSE: Unexpected Consequences and a Refined Solution

In an attempt to optimize a large-scale update operation on a MyISAM table, a developer employed the CASE feature. However, the query exhibited unexpected behavior, impacting the performance and affecting all rows in the table, even those not explicitly mentioned in the CASE statement.

The initial query:

UPDATE `table` SET `uid` = CASE
    WHEN id = 1 THEN 2952
    WHEN id = 2 THEN 4925
    WHEN id = 3 THEN 1592
    END

Unexpectedly, this query updated every row in the table with 25 million records, setting all unspecified rows to NULL. This behavior contradicts the expected functionality of the CASE statement, where unmentioned rows should remain unchanged.

The reason for this unexpected outcome lies in the absence of an ELSE clause in the CASE statement. When the id of a row does not match any of the specified conditions, the CASE expression evaluates to NULL. Consequently, MySQL updates all rows with NULL, regardless of whether they were originally specified in the CASE statement.

To rectify this issue and perform a targeted update, the query should include an ELSE clause:

UPDATE `table` SET `uid` = CASE
    WHEN id = 1 THEN 2952
    WHEN id = 2 THEN 4925
    WHEN id = 3 THEN 1592
    ELSE `uid`
    END
WHERE id  in (1,2,3)

The updated query adds an ELSE clause to the CASE statement, ensuring that all unmentioned rows retain their original value for uid. Additionally, a WHERE clause is introduced to restrict the update operation to only the specified ids (1, 2, and 3), further improving efficiency by limiting the affected rows.

The above is the detailed content of Why Does My MySQL CASE WHEN Update Affect All Rows, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!