MySQL 在一次 SQL 查询中更新多行的多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18091318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update multiple columns for multiple rows in one query of SQL
提问by Pankaj Gadge
I am trying to set multiple columns for multiple rows in one query, but so far no luck.
我试图在一个查询中为多行设置多列，但到目前为止没有运气。
Here's how my table looks like
这是我的桌子的样子
Table: user
表：用户
I would like to set 'ext_id' on user_id IN (3,4,5) and also like to set ext_flag = Y and admin_role = admin on the same rows.
我想在 user_id IN (3,4,5) 上设置 'ext_id' 并且还想在同一行上设置 ext_flag = Y 和 admin_role = admin。
the resulting table looks like follows
结果表如下所示
My query looks like this, but I am getting erros due to unfamiliarity to SQL syntax.
我的查询看起来像这样，但由于不熟悉 SQL 语法，我遇到了错误。
update user
set ext_flag = 'Y', admin_role = 'admin', ext_id =
case
when user_id = 2 then 345
when user_id = 4 then 456
when user_id = 5 then 789
end
I am having hard time in SET syntax with multiple columns.
我在多列的 SET 语法中遇到困难。
回答by echo_Me
try this
尝试这个
update user
set ext_flag = 'Y', admin_role = 'admin', ext_id =
case
when user_id = 2 then 345
when user_id = 4 then 456
when user_id = 5 then 789
end
**WHERE user_id in (2,4,5)**
回答by camille khalaghi
You can also hack the insert operation :
您还可以破解插入操作：
INSERT INTO mytable (id, a, b, c)
VALUES (1, 'a1', 'b1', 'c1'),
(2, 'a2', 'b2', 'c2'),
(3, 'a3', 'b3', 'c3'),
(4, 'a4', 'b4', 'c4'),
(5, 'a5', 'b5', 'c5'),
(6, 'a6', 'b6', 'c6')
ON DUPLICATE KEY UPDATE id=VALUES(id),
a=VALUES(a),
b=VALUES(b),
c=VALUES(c)
