How to do nothing in a SQL case statement?

Question

I need to update a column conditionally and in some cases not update it at all. this is the best way I can do this, but it looks like it might be inefficient in ELSE as it is still updating the column with the current column value. Is there a better way that ELSE can simply "do nothing" at all.

UPDATE mytable SET col1 = CASE WHEN (col1 = 20) THEN 10 ELSE col1 END 

Answer 1

Apply WHERE :

 UPDATE mytable SET col1 = 10 WHERE col1 = 20 

However, if your update is more complex and you really need a few CASE , you need to either bite the bullet, omit WHERE , or add all the columns in WHERE that 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 can still “update” the columns unnecessarily (for their old values), but not for full rows.