mysql更新多行，每行都有自己的值，用一个CASE语句 [英] mysql update multiple rows, each with its own values, with a CASE statement

问题描述

我正在尝试一次更新多行的两个字段，但我无法确定这样做的正确语法，除非更新一个字段.

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.

每一行都由一个 id 标识，因此我使用的是 CASE 语句.

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

我有这张桌子:

tbl_accounts(id_account, national_id,group_id)

tbl_accounts(id_account, nation_id, group_id)

现在，以下查询用于更新仅一个字段:

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

以上将更新由其 id_account 标识的每个对应行的 national_id 字段.

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

并且以下查询不起作用用于更新两个字段 - 请建议对语法进行修复.我正在尝试避免使用任何 SELECT/JOIN/etc':

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.

非常感谢任何帮助！

推荐答案

听起来您正在寻找这样的东西:

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

但在这种情况下进行单独更新是一个明智的解决方案.上面的查询将需要检查表中的每一行以查看它是否与条件匹配.如果您在 id_account 上有一个索引(并且您可能因为它看起来是主键而这样做)，那么更新单行会非常快.

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

这篇关于mysql更新多行，每行都有自己的值，用一个CASE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！