MERGE 语句在 SQL Server / Oracle 中的 upsert 完整模板

SQL Server的MERGE必须同时包含WHEN MATCHED和WHEN NOT MATCHED分支，否则报错；Oracle则允许省略任一分支。两者在参数占位符、NULL处理、USING语法及索引要求上存在显著差异，跨库移植需方言适配。

SQL Server 的 MERGE 语法必须带 WHEN MATCHED 和 WHEN NOT MATCHED

SQL Server 不允许只写一半逻辑——哪怕你只想更新、不想插入，也得显式写出 WHEN NOT MATCHED THEN INSERT ... VALUES (...)（哪怕里面是空操作或 throw 错误），否则报错 The MERGE statement attempted to UPDATE or DELETE the same row more than once 或直接语法拒绝。

常见错误是漏掉 WHEN NOT MATCHED 分支，或把 INSERT 写成 INSERT INTO ... SELECT 却没加 VALUES 子句；正确模板如下：

MERGE target_table AS t
USING (SELECT @id AS id, @name AS name, @age AS age) AS s
ON t.id = s.id
WHEN MATCHED THEN
  UPDATE SET name = s.name, age = s.age
WHEN NOT MATCHED THEN
  INSERT (id, name, age) VALUES (s.id, s.name, s.age);

• USING 子句必须返回结果集，不能直接用值列表（如 USING (VALUES (@id, @name)) 是合法的，但需指定列别名）
• ON 条件里不能出现 NULL 比较（t.id = s.id 在任一为 NULL 时恒为 UNKNOWN，导致匹配失败，应提前过滤或用 IS NULL 显式处理）
• 若目标表有触发器，MERGE 可能被拆成多个语句执行，触发器行为与单独 UPDATE/INSERT 不一致

Oracle 的 MERGE 允许省略 INSERT 或 UPDATE 分支

Oracle 更宽松：你可以只写 WHEN MATCHED THEN UPDATE（实现“仅更新不插入”），或只写 WHEN NOT MATCHED THEN INSERT（实现“仅插入不更新”），不会报错。但要注意 UPDATE 后必须跟 SET，且不能写 WHERE 子句在 UPDATE 后面（那是 SQL Server 写法，Oracle 不支持）。

标准 upsert 模板如下：

知鹿匠教师AI工具，新课标教案_AI课件PPT_作业批改

MERGE INTO target_table t
USING (SELECT :id AS id, :name AS name, :age AS age FROM DUAL) s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name, t.age = s.age
WHEN NOT MATCHED THEN
  INSERT (id, name, age) VALUES (s.id, s.name, s.age);

• USING 必须带 FROM DUAL（即使单行），否则报 ORA-00928: missing SELECT keyword
• ON 条件支持 IS NULL，比如 t.id = s.id OR (t.id IS NULL AND s.id IS NULL) 可用于处理空值键
• Oracle 12c+ 支持 INSERT ... ON DUPLICATE KEY UPDATE 风格的 INSERT 语句，但那不是 MERGE，而是独立语法，兼容性不如 MERGE

跨数据库移植时最常踩的坑：参数占位符和空值处理

SQL Server 用 @param，Oracle 用 :param 或 &param，拼错就直接报错；更隐蔽的是空值逻辑——SQL Server 的 = 对 NULL 返回 UNKNOWN，Oracle 同样如此，但开发者常默认它“等于 false”，结果 ON t.id = s.id 在 s.id 为 NULL 时永远不匹配，导致意外插入重复空键记录。

• 统一做法：在 USING 子句中对可能为空的键字段做 COALESCE(s.id, -999999)（SQL Server）或 NVL(s.id, -999999)（Oracle），并在目标表对应字段建函数索引（如需要）
• 不要依赖客户端传入非空校验——数据库层必须兜底，否则并发下容易产生脏数据
• SQL Server 中 MERGE 是语句级原子操作，但内部可能分多步执行；Oracle 的 MERGE 同样不保证单次锁全表，高并发时仍需考虑唯一索引 + 重试机制

性能关键点：ON 条件必须走索引，且避免复杂表达式

无论 SQL Server 还是 Oracle，MERGE 的 ON 条件如果无法利用索引（比如写成 UPPER(t.code) = UPPER(s.code)），就会触发全表扫描，upsert 100 行可能比 100 次单独 UPDATE 还慢。

• 确保 ON 左侧字段（目标表）上有索引，且类型与右侧完全一致（比如 INT 对 INT，不是 INT 对 VARCHAR）
• 避免在 ON 中调用函数、类型转换、ISNULL/NVL——这些都会让索引失效
• SQL Server 中，如果 USING 是大结果集（如子查询返回万级行），建议先插入临时表并建索引，再 MERGE，否则优化器容易选错执行计划

实际写的时候，别指望一套 SQL 能同时跑通两个库。空值逻辑、占位符、函数名、甚至是否允许省略分支，都得按目标数据库切开处理。最稳妥的方式是在 ORM 层或 DAO 方法里做数据库方言适配，而不是硬塞一个“通用”语句。