使用多个值更新多行(同一列)

Question

我有阵列( 406 => 1 407 => 3 408 => 2 409 => 7 410 => 1)

作为mysql查询运行

UPDATE counter SET total = 1 WHERE id = 406;
UPDATE counter SET total = 3 WHERE id = 407;
UPDATE counter SET total = 2 WHERE id = 408;
UPDATE counter SET total = 7 WHERE id = 409;
UPDATE counter SET total = 1 WHERE id = 410;

我只能通过分组相同的合计值来优化上面的查询，如下所示：

UPDATE counter
    SET total = 1
    WHERE name IN (406, 410);

有没有更好地优化它的方法，而不是逐个执行(循环)更新查询。

Answer 1

你需要这样做：

UPDATE counter SET total = CASE 
   WHEN id = 406 THEN 1
   WHEN id = 407 THEN 3
   WHEN id = 408 THEN 2
   WHEN id = 409 THEN 7
   WHEN id = 410 THEN 1
   END

Answer 2

您可以使用键值对进行更新，如下所示

UPDATE counter SET total = '".$value."' WHERE id = '".$key."';