Updating using multiple query vs case recommendation

Question

I was looking around updating using multiple queries vs using case when I came across this answer. SQL Statement with multiple SETs and WHEREs

and I came across this answer which said is NOT recommended.

UPDATE  table
SET ID = CASE WHEN ID = 2555 THEN 111111259 
            WHEN ID =2724 THEN 111111261
            WHEN ID = 2021 THEN 111111263
            WHEN ID = 2017 THEN 111111264
          END
WHERE ID IN (2555,2724,2021,2017)

Why is this not recommended? Isn't it better since you are only going through the table once instead of multiple times with multiple queries. Especially in a large table?

Are you sure you want to change your `ID` column? I'm assuming that's your `PK`. — SS_DBA, Jul 19 '17 at 14:52

score 0 · Accepted Answer · answered Jul 19 '17 at 14:52

In terms of performance, it is unlikely to make a difference. The expensive part is the IN clause, and that's not faster than multiple WHERE clauses, in my experience. It may be slower - because as well as finding the rows to operate on, it then has to chose which row to update with which setting.

The main reason this is not recommended is because the query repeats itself, thus offending against the DRY principle. You're repeating the IDs of the rows to affect, so you're inviting bugs.

Can you spot what's wrong with this version of your query?

UPDATE  table
SET ID = CASE WHEN ID = 2555 THEN 111111259 
            WHEN ID =2724 THEN 111111261
            WHEN ID = 2021 THEN 111111263
            WHEN ID = 2017 THEN 111111264
          END
WHERE ID IN (2555,2724,2021,2071)

(Hint: 2017 and 2071).

This gets even worse if the query is more complex, with more WHERE clauses - did you only get "3 rows affected" because one of the other where clauses didn't match, or was it because you got an ID wrong?

Updating using multiple query vs case recommendation

1 Answers1