Can this be run as a single update query?
mysql, php, sql
Solution
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?).
Problem
``` 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?