使用一次查询更新多行数据

Question

我可以使用一个查询来更新多行吗？

如何将以下查询进行联合：

UPDATE tablename SET col1='34355' WHERE id='2'

UPDATE tablename SET col1='152242' WHERE id='44'

- ucha

Answer 1

您可以使用虚拟地图表来进行此更新。

update tablename
inner join (
    select '34355'  col1, '2'  id union all
    select '152242' col1, '44' id
) map on map.id = tablename.id
set tablename.col1 = map.col1

使用这种模式可以方便地扩展（只需向地图添加行）。 这还使得MySQL更可预测地选择tablename.id上的索引进行常规JOIN操作。

- RichardTheKiwi

Answer 2

你可以这样做吗？当然可以。但是你应该这样做吗？绝对不行。

考虑一下五年后看你的代码的人。哪个更易读，这个：

UPDATE tablename SET col1='34355' WHERE id='2';
UPDATE tablename SET col1='152242' WHERE id='44';

或者这样（Scrum大师的答案）：

UPDATE tablename SET col1 = IF(id='2', '34355','152242') WHERE id='2' OR id='44';

第二个方法更短，但是很难确切地理解它在做什么。如果你担心竞态条件，可以将其设置为单个事务（在大多数现代DBMS中）。

BEGIN;
UPDATE tablename SET col1='34355' WHERE id='2';
UPDATE tablename SET col1='152242' WHERE id='44';
COMMIT;

这样，当更新第2行但不更新第44行时，您可以确保没有其他查询会运行。

- Plutor

Answer 3

你可以使用 OR 子句与 IF() 函数（或其他 RDBMS 的 CASE WHEN...）组合

UPDATE tablename SET col1 = IF(id='2', '34355','152242')
WHERE id='2' OR id='44'

- The Scrum Meister

---

对于一长串的更新，最好只做单个更新查询，而不是构建这个丑陋的怪物。这个版本唯一的好处是，在非事务感知的数据库上，它可能会模拟事务。 - Marc B

@marc-b OP 询问了关于2行的问题。如果列表很大，另一个缺点是解析器可能会选择表扫描而不是索引合并。 - The Scrum Meister

强烈建议单独查询。答案是正确的，但我建议不要使用它。 - Kriem

在大量更新大量行的情况下，单独的查询效率要低得多。使用 IF 或 CASE 可使表只被扫描一次而非多次。 - JNK

@jnk 很有可能 id 已经被索引了，所以单个查询不会进行表扫描。我相信 cyberwiki 的答案是最好的。 - The Scrum Meister

@Scrum - 对于我不够准确的术语表示，我道歉...如果您有多个“UPDATE”语句，则每个语句都将读取/索引查询/扫描表。如果您使用带有“CASE”的更新语句，则仅一次处理每行。 - JNK

Answer 4

通常情况下，您只能在单个查询中更新多行数据，如果您的where子句匹配多行数据...那么每一行都将设置相同的值。

除此之外，您可以在set子句中使用表达式来进行奇怪的操作，但通常情况下，执行多个查询会更加清晰，除非有非常特殊的原因不能这样做。

- Jake Staines