Why Does My MySQL MyISAM UPDATE with CASE WHEN Fail and How Can I Fix It?

Updating MySQL MyISAM Tables Using CASE WHEN/THEN/ELSE

Q: When attempting to update specific rows in a large MyISAM table using a CASE statement, the query consumes excessive CPU and updates all rows, including those not specified. What can be done to limit the update to the intended rows?

A: The issue arises when the CASE statement does not include an ELSE clause. By default, rows not explicitly mentioned in the CASE statement are assigned NULL values. This results in unnecessary updates across the entire table.

To resolve this, append an ELSE clause to the CASE statement, assigning the existing value of the uid column to it. This will prevent the update from affecting rows not specified in the WHEN clauses.

Additionally, include a WHERE clause to explicitly specify the rows that should be updated. This ensures that only the desired rows are targeted.

Example:

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 above is the detailed content of Why Does My MySQL MyISAM UPDATE with CASE WHEN Fail and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!