具有不同值的MySQL批量更新语句

Question

是否有与这些等效的单个 SQL 语句？

UPDATE table_name SET (a = 'something', b='B1') WHERE id=1;
UPDATE table_name SET (a = 'something else', b='B2') WHERE id=2;
UPDATE table_name SET (a = 'another', b='B3') WHERE id=3;

Answer 1

是的，这个:

UPDATE table_name
   SET a = CASE WHEN id = 1
                THEN 'something'
                WHEN id = 2
                THEN 'something else'
                WHEN id = 3
                THEN 'another'
           END
 WHERE id IN (1,2,3)
;

但我不确定这是否是您的想法？