mysql - 更新集，匹配单个列，单个查询中不同 ID 的多个值

Question

我该怎么做:

UPDATE `TEST_TABLE` SET `C1` = 'VALUE_FOR_ID1' WHERE `ID` = 1;
UPDATE `TEST_TABLE` SET `C1` = 'VALUE_FOR_ID2' WHERE `ID` = 2;
UPDATE `TEST_TABLE` SET `C1` = 'VALUE_FOR_ID3' WHERE `ID` = 3;

使用具有匹配项的单个查询(类似这样):

UPDATE `TEST_TABLE` SET `C1` = ('VALUE_FOR_ID1','VALUE_FOR_ID2','VALUE_FOR_ID3') WHERE `ID` IN (1,2,3);

Answer 1

你应该能够使用CASE来做到这一点:

UPDATE  TEST_TABLE SET C1 =  
CASE
  WHEN ID = 1 THEN 'VALUE_FOR_ID1'
  WHEN ID = 2 THEN 'VALUE_FOR_ID2'
  WHEN ID = 3 THEN 'VALUE_FOR_ID3'
END
WHERE ID IN (1,2,3)