MySQL UPDATE with CASE: Why Does it Update All Rows and How to Fix It?

MySQL Update with CASE: Unexpected Behavior and Solution

When utilizing the CASE statement in MySQL UPDATE queries, it's important to understand its impact on data. In the provided example, the original query intended to update specific rows based on their id field. However, the query unexpectedly updated all 25 million rows, introducing NULL values.

This behavior stems from the nature of the CASE statement. Without an ELSE clause, MySQL interprets any missing conditions as equaling NULL. Therefore, all rows that did not match the specified id values were updated with NULL for the uid column.

To resolve this issue and selectively update specific rows while preserving other data, consider using an ELSE clause to provide a default value or maintain the existing value. Additionally, specify the rows that need to be updated using the WHERE clause.

For instance, the following modified query addresses these concerns:

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)

This query now updates only rows with id values of 1, 2, and 3, leaving the remaining rows unaffected. By combining the CASE statement with the ELSE clause and WHERE clause, you can efficiently update specific rows without inadvertently modifying other data in the table.

The above is the detailed content of MySQL UPDATE with CASE: Why Does it Update All Rows and How to Fix It?. For more information, please follow other related articles on the PHP Chinese website!