How to do nothing in an SQL case statement?
mysql, performance, sql
Solution
Apply a `WHERE`:
UPDATE mytable
SET col1 = 10
WHERE col1 = 20
However, if your update is more complex and you actually need multiple `CASE` you either have to bite the bullet and omit the `WHERE` or you have to add all columns in the `WHERE` which you want to update:
UPDATE mytable
SET col1 = CASE WHEN (col1 = 20) THEN 10 ELSE col1 END,
SET col2 = CASE WHEN (col2 = 40) THEN 20 ELSE col2 END,
SET col3 = CASE WHEN (col3 = 80) THEN 30 ELSE col3 END
WHERE col1 = 20 OR col2 = 40 OR col3 = 80
This might still "update" columns unnecessarily(to their old values) but not complete rows.
Problem
I need to update a column conditionally and in some cases not update it at all. this is the best way i can figure to do that but it seems like it may be inefficient in the ELSE as it's still updating the column with the current value of the column. Is there a better way in which the ELSE can just "do nothing" at all. ``` UPDATE mytable SET col1 = CASE WHEN (col1 = 20) THEN 10 ELSE col1 END ```