mysql update multiple rows, each with its own values, with a CASE statement

Question

Hello!

I'm trying to update two fields of several rows at once but I can't determine the right syntax to do so, except for doing so with one field update.

Each row is identified by an id, and therefore I'm using a CASE statement.

I have this table:

tbl_accounts(id_account, nation_id, group_id)

Now, the following query works for updating only one field:

UPDATE tbl_accounts SET nation_id = CASE id_account
WHEN 3 THEN 333
WHEN 5 THEN 555
ELSE nation_id END

The above will update the nation_id field of each corresponding row identified by its id_account.

And the following query doesn't work for updating two fields - please suggest a fix to the syntax. I'm trying to avoid using any SELECT/JOIN/etc':

UPDATE tbl_accounts SET nation_id = CASE id_account, group_id = CASE id_account
WHEN 3 THEN 3331, 3332
WHEN 5 THEN 5551, 5552
ELSE nation_id, group_id END

I could run this as two separate statements but I'm sure there's a way to combine the two into one.

Any help is highly appriciated!

Answer 1

It sounds like you are looking for something like this:

UPDATE tbl_accounts
SET nation_id =
     CASE id_account
     WHEN 3 THEN 3331
     WHEN 5 THEN 5551
     ELSE nation_id
     END,
group_id =
     CASE id_account
     WHEN 3 THEN 3332
     WHEN 5 THEN 5552
     ELSE group_id
     END

But doing separate updates is a sensible solution in this situation. The above query will require checking every row in the table to see if it matches the condition. If you have an index on id_account (and presumably you do as it appears to be the primary key) then it will be very fast to update a single row.

UPDATE tbl_accounts SET nation_id = 3331, groupid = 3332 WHERE id_account = 3
UPDATE tbl_accounts SET nation_id = 5551, groupid = 5552 WHERE id_account = 5