使用一个查询更新多行? [英] Update multiple rows with one query?

问题描述

我在这里找到了一些可以更新一个字段的东西:

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/

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!

推荐答案

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)

这篇关于使用一个查询更新多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！