如何在单条SQL查询中更新多行不同值且不插入不存在的行

需求说明 #

你需要通过单条SQL完成以下操作：

• 批量将多行的column3列更新为不同的指定值
• 仅主键id匹配的行执行更新，id不存在的行直接忽略，绝对不产生插入操作
• 支持大批量待更新行的场景，避免单条UPDATE循环执行的低效问题

核心实现方案 #

采用UPDATE关联自定义映射派生表的方式实现，关联条件为主键id相等，仅原表存在的id会触发更新，完全无插入风险。

示例代码 #

首先是初始表结构与测试数据：

CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
column2 INT NOT NULL,
column3 INT NOT NULL);

INSERT INTO t1
VALUES
(1, 1, 10),
(7, 2, 20);

替换原INSERT ON DUPLICATE KEY UPDATE的批量更新语句如下：

-- 单条批量更新，仅匹配id存在的行，id=5不存在直接忽略无插入
UPDATE t1
INNER JOIN (
  VALUES
  ROW(1, 2),
  ROW(5, 3),
  ROW(7, 4)
) AS new_vals(id, column3_new)
ON t1.id = new_vals.id
SET t1.column3 = new_vals.column3_new;

执行后查询结果：

SELECT * FROM t1;

返回结果如下，无id=5的新行生成：

低版本MySQL兼容写法 #

如果你的MySQL版本不支持VALUES ROW语法，可改用UNION ALL构造映射表，效果完全一致：

UPDATE t1
INNER JOIN (
  SELECT 1 AS id, 2 AS column3_new UNION ALL
  SELECT 5 AS id, 3 AS column3_new UNION ALL
  SELECT 7 AS id, 4 AS column3_new
) AS new_vals
ON t1.id = new_vals.id
SET t1.column3 = new_vals.column3_new;

内容的提问来源于stack exchange，提问作者John