SQL MERGE冲突及同表作为源目标时更新失效问题咨询

问题1：两段MERGE语句执行表现差异原因 #

MERGE语法有强制约束：单个目标表行最多只能匹配到1个源表行，如果出现一个目标行匹配多个源行的情况，就会触发匹配冲突报错。

两段代码的核心差异是过滤条件的位置：

• 冲突代码仅将new.ID = old.ID放在ON匹配子句中：如果同一个ID在源表TBL2中存在多条历史记录，会直接导致TBL1的同一个目标行匹配到多个源行，WHEN MATCHED后的附加过滤条件仅用来判断是否执行更新，不会改变匹配阶段已经产生的一对多匹配关系，因此触发冲突报错。

-- 冲突代码
MERGE INTO  TBL1 as new USING   TBL2 as old
ON (new.ID= old.ID)
WHEN MATCHED  AND new.FILE_DATE = old.END_DATE AND old.PRODUCT != 'PRIOR_VALUE' AND new.PRODUCT = 'PRIOR_VALUE' 
THEN  UPDATE SET new.PRODUCT = old.PRODUCT ; 

• 正常运行代码将所有过滤条件都放到了ON子句中：在匹配阶段就直接筛选出唯一符合业务逻辑的源行和目标行的对应关系，不会出现一对多匹配的情况，因此可以正常执行。

-- 正常运行代码
MERGE INTO  TBL1 as new USING   TBL2 as old 
ON (new.ID= old.ID AND new.FILE_DATE = old.END_DATE AND old.PRODUCT != 'PRIOR_VALUE' AND new.PRODUCT = 'PRIOR_VALUE' ) 
WHEN MATCHED  
  THEN  UPDATE SET new.PRODUCT = old.PRODUCT ;

问题2：同表作为源和目标时无更新的原因 #

绝大多数数据库的MERGE执行逻辑是：先对源表做一致性快照读，读取所有源数据完成匹配判断后，再执行目标表的更新操作。
当源表和目标表为同一张表时，会出现两个问题：

1. 读快照的时机早于更新操作，数据库会禁止对正在作为源读取的行做修改，避免读写一致性冲突
2. 部分数据库的MERGE实现本身不支持自引用的更新操作，即使语法校验通过，执行阶段也会自动跳过匹配到的更新逻辑

你当前的匹配逻辑需要取同一个ID下的前序历史行做更新，用同表自匹配的方式本身也容易出现匹配逻辑混乱，建议还是用两张表或者先构造带前序值的临时表作为源再执行MERGE。

-- 无更新代码
MERGE INTO  TBL1 as new USING   TBL1 as old
ON (new.ID= old.ID AND new.FILE_DATE = old.END_DATE AND old.PRODUCT != 'PRIOR_VALUE' AND new.PRODUCT = 'PRIOR_VALUE' )
WHEN MATCHED  
THEN  UPDATE SET new.PRODUCT = old.PRODUCT ; 

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