SQL MERGE 操作在不同数据库的应用

mysql不支持merge，须用insert…on duplicate key update替代，且依赖唯一索引；postgresql merge要求严格匹配与完整when子句；sql server merge易因源数据重复导致错误；oracle merge的delete where不可引用源表字段。

MySQL 没有 MERGE，得用 INSERT … ON DUPLICATE KEY UPDATE 代替

MySQL 根本不支持 MERGE 语句，直接写会报错 Error 1064 (42000)。这不是语法写错，是引擎压根没实现。如果你从 PostgreSQL 或 SQL Server 迁移脚本，第一反应往往是“怎么执行不了”，其实卡在这儿。

实际替代方案是 INSERT ... ON DUPLICATE KEY UPDATE，但它只在有唯一索引（UNIQUE 或 PRIMARY KEY）的字段上生效。没建索引？它就变成纯插入，不会更新。

• 必须确保目标表至少有一个 UNIQUE 或 PRIMARY KEY 约束，否则无法触发“重复时更新”逻辑
• ON DUPLICATE KEY UPDATE 只能更新一行，不能像标准 MERGE 那样根据条件分支做 INSERT/UPDATE/DELETE
• 如果主键或唯一键由多列组成，所有列都匹配才算“重复”，别漏掉组合约束

示例：INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'a@b.com') ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);

PostgreSQL 的 MERGE 要求明确指定 MATCH 条件和 WHEN 子句

PostgreSQL 15+ 才原生支持 MERGE，但语法比 SQL Server 严格得多：必须写全 WHEN MATCHED 和 WHEN NOT MATCHED，不能省略；而且 using 子句里的源数据必须是可扫描的——比如不能直接用 VALUES 列表嵌套太深，容易触发 ERROR: cannot reference transition tables from within a trigger。

常见误用是把 MERGE 当成万能 UPSERT，结果发现更新行为不符合预期。根本原因是没理解“MATCH”到底匹配什么：它只基于 ON 后的等值条件，不支持 >、IS NULL 这类非等值判断。

• ON 条件里只能用 =，其他比较符会导致语法错误
• 每个 WHEN 分支必须带 THEN，且只能有一个 INSERT、UPDATE 或 DELETE 动作
• 如果源数据有重复键导致多行匹配目标行，PostgreSQL 会报错 ERROR: merge action would affect more than one row，不像 SQL Server 会静默选一行

示例：MERGE INTO products p USING (VALUES (1, 'NewName')) AS new(id, name) ON p.id = new.id WHEN MATCHED THEN UPDATE SET name = new.name WHEN NOT MATCHED THEN INSERT VALUES (new.id, new.name);

SQL Server 的 MERGE 有隐藏陷阱：重复执行可能出错

SQL Server 的 MERGE 看似最接近标准，但有个关键限制：源数据集不能有重复键值映射到同一个目标行，否则运行时报 Msg 8672, Level 16 错误。这个错误不是立刻出现，而是在并发场景或批量处理时偶然爆发，很难复现。

更麻烦的是，MERGE 在事务中若中途失败，部分操作可能已提交（尤其涉及触发器时），导致数据不一致。微软官方文档都明确建议：除非你完全控制源数据去重逻辑，否则优先用独立的 UPDATE + if @@ROWCOUNT = 0 INSERT 替代。

• 务必在 USING 子句前对源数据按匹配键 GROUP BY 或加 DISTINCT 去重
• 避免在 MERGE 中调用含副作用的函数（如 GETDATE() 多次调用可能返回不同值）
• 如果目标表有 INSTEAD OF 触发器，MERGE 行为会彻底改变，甚至跳过某些 WHEN 分支

示例安全写法：WITH dedup AS (select id, MAX(name) AS name FROM staging GROUP BY id) MERGE ... USING dedup ...

Oracle 的 MERGE 不支持 DELETE 子句中的复杂 WHERE 条件

Oracle 的 MERGE 允许 WHEN MATCHED THEN UPDATE ... DELETE WHERE，但这个 DELETE WHERE 只能引用目标表字段，不能引用源表别名（比如不能写 DELETE WHERE s.flag = 'Y'），否则报 ORA-00904: "S"."FLAG": invalid identifier。

这导致一个典型需求卡住：想“当源数据标记为删除时，把目标行删掉”。你得绕道——要么提前把源数据过滤进临时表，要么拆成两步：先 UPDATE 标记软删除字段，再单独 DELETE。

• DELETE WHERE 后的表达式里，只认目标表别名（如 t.status），源表字段不可见
• UPDATE 和 DELETE 不能共存于同一个 WHEN MATCHED 分支，必须用两个独立分支（WHEN MATCHED THEN UPDATE ... 和 WHEN MATCHED THEN DELETE ...），但 Oracle 不允许这样写，只能二选一
• 如果 ON 条件用了函数索引字段（如 UPPER(email)），确保目标表该列上有对应函数索引，否则性能极差

示例受限写法：MERGE INTO customers t USING updates s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.name = s.name DELETE WHERE t.status = 'INACTIVE'; —— 注意 DELETE WHERE 里没有 s。

跨数据库写 MERGE 最容易翻车的地方，从来不是语法本身，而是各厂商对“匹配”的定义差异、对源数据重复的容忍度、以及错误发生时的恢复行为。这些细节不跑真实数据、不压测并发，根本看不出来。