同一列但具有多个WHERE子句的MYSQL UPDATE SET [英] MYSQL UPDATE SET on the Same Column but with multiple WHERE Clauses

问题描述

对于MYSQL，我正在使用以下查询:

With MYSQL I'm using this query:

UPDATE CustomerDetails_COPY
SET Category_ID = 10
WHERE Category_ID = 2

很好，但是我想向其添加15个以上的SET/WHERE:

Thats fine but I'd like to ad 15+ more SET/WHERE to it like:

UPDATE CustomerDetails_COPY
SET Category_ID = 9  WHERE Category_ID = 3
SET Category_ID = 12 WHERE Category_ID = 4
SET Category_ID = 11 WHERE Category_ID = 5
.....

我要如何添加呢?

根据猎人的建议:

UPDATE CustomerDetails_COPY
    SET Category_ID = CASE Category_ID
        WHEN 2 THEN 10 
        WHEN 3 THEN 9
        WHEN 4 THEN 12
        WHEN 5 THEN 11
    END
WHERE Category_ID IN (2,3,4,5)

这很好！谢谢

推荐答案

类似的方法应该对您有用:

Something like this should work for you:

UPDATE CustomerDetails_COPY
    SET Category_ID = CASE Category_ID
        WHEN 2 THEN 10 
        WHEN 3 THEN 9
        WHEN 4 THEN 12
        WHEN 5 THEN 11
    END
WHERE Category_ID IN (2,3,4,5)

或者，如西蒙(Simon)所建议的，您可以这样做来避免两次输入值:

Alternatively, as Simon suggested, you could do this to save from entering the values twice:

UPDATE CustomerDetails_COPY
    SET Category_ID = CASE Category_ID
        WHEN 2 THEN 10 
        WHEN 3 THEN 9
        WHEN 4 THEN 12
        WHEN 5 THEN 11
        ELSE Category_ID
    END

来源: 查看全文