用一个查询更新多行？

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!

当然这不起作用。尝试了其他一些组合并失败了。任何想法？谢谢！

2 个解决方案

#1

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)

#2

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)