Can this be run as a single update query?

Question

UPDATE userTable 
SET userAge=245, userName="fred"  WHERE userId = 321, 
SET userAge=32, userName="dave" WHERE userId = 424;

Is there a better way to write this code?

Answer 1

Yes, using case statements:

UPDATE userTable 
    SET userAge= (case when userId = 321 then 245 else 32 end),
        userName= (case when userId = 321 then 'fred' else 'dave' end)
    WHERE userId in (321, 424);

However, I think a more general way to write this is using join syntax:

UPDATE userTable join
       (select 321 as UserId, 'fred' as userName, 245 as userAge union all
        select 424, 'dave', 32
       ) toupdate
       on userTable.userId = toupdate.UserId
    set userTable.userAge = toupdate.userAge,
        userTable.userName = toupdate.userName;

This makes it easier to add more rows, and shows the power of using join with update.

EDIT:

About performance. Two updates require setting up two transactions in the database; one update requires only one. So, one update is likely to be a wee bit faster. The performance difference would only be noticeable if you had no index on userTable(userId). With such an index, both versions (with the where clause and using join) should use the index to find the rows to update quite quickly.

But, there is a more important difference. Two updates leave the table in an inconsistent state "between" the updates -- the user ids and names won't be consistent between these updates. If the second one fails or someone uses the table, they'll have inconsistent data. You want to do the two updates at the same time (you could also fix this by using explicit transactions, but why bother?).

Answer 2

UPDATE userTable 
SET userAge =  case when userId = 321 then 245
                    when userId = 424 then 32
               end,
    userName = case when userId = 321 then "fred"
                    when userId = 424 then "dave"   
               end      
WHERE userId in (321, 424)