MySQL 用一个查询更新多行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2528181/
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 rows with one query?
提问by datasn.io
I found something that works with updating one field at here: http://www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/
我在这里找到了可以更新一个字段的东西：http: //www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/
UPDATE person
SET name = CASE id
WHEN 1 THEN 'Jim'
WHEN 2 THEN 'Mike'
WHEN 3 THEN 'Precious'
END
WHERE id IN (1,2,3)
My question is how to update more than one field? Such as:
我的问题是如何更新多个字段？如：
UPDATE person
SET name = CASE, sex = CASE id
WHEN 1 THEN 'Jim', 'female'
WHEN 2 THEN 'Mike', 'male'
WHEN 3 THEN 'Precious', 'male'
END
WHERE id IN (1,2,3)
Which doesn't work of course. Tried a few other combination and failed. Any idea? Thanks!
这当然行不通。尝试了其他一些组合并失败了。任何的想法？谢谢！
回答by Ayman Hourieh
UPDATE person
SET name = CASE id
WHEN 1 THEN 'Jim'
WHEN 2 THEN 'Mike'
WHEN 3 THEN 'Precious'
END,
sex = CASE id
WHEN 1 THEN 'female'
WHEN 2 THEN 'male'
WHEN 3 THEN 'male'
END
WHERE id IN (1,2,3)
回答by Ashish Gupta
Have you tried something like below. You can have multiple "SET" statements one for each column.
你有没有尝试过类似下面的东西。您可以为每一列设置多个“SET”语句。
UPDATE person
SET name = CASE id
WHEN 1 THEN 'Jim'
WHEN 2 THEN 'Mike'
WHEN 3 THEN 'Precious'
END,
sex = CASE id
WHEN 1 THEN 'female'
WHEN 2 THEN 'male'
WHEN 3 THEN 'male'
END
WHERE id IN (1,2,3)
