SQL Server MERGE语句中DELETE子句误删全表问题求解

问题根因 #

你遇到的全表删除问题是因为 WHEN NOT MATCHED BY SOURCE 子句的默认触发逻辑是：所有未和源表满足ON子句匹配条件的目标表行，都会被删除，而不仅仅是你要操作的 sid = @sid 范围内的行。哪怕是目标表中sid不等于@sid的无关行，因为不满足dest.sid = @sid的匹配条件，也会被判定为"源表未匹配"进而被删除。如果存在字段类型不匹配导致的隐式转换使得匹配失效，甚至会出现同sid下的行全部被误删的情况。

解决方案 #

只需在 WHEN NOT MATCHED BY SOURCE 子句后追加过滤条件，限制删除范围仅为当前要操作的sid即可，修改后的完整SQL如下：

--use test
declare @sid varchar(100) = '5'
declare @uid as userid

insert into @uid(userid) values(2)
insert into @uid(userid) values(3)
--insert into @uid(userid) values(5)    // 已注释该行
insert into @uid(userid) values(6)

MERGE INTO dbo.test_master AS dest
USING @uid AS sou ON
    dest.sid = @sid
    AND sou.userid = dest.testid
WHEN MATCHED THEN 
    -- 匹配成功的行testid已经一致，无需更新可直接删除此段逻辑
    UPDATE SET dest.testid = sou.userid
WHEN NOT MATCHED THEN
    INSERT( sid, testid )
    VALUES( @sid, sou.userid )
-- 新增过滤条件，仅删除当前sid下未匹配到源的行
WHEN NOT MATCHED BY SOURCE AND dest.sid = @sid
    THEN DELETE
;

额外优化建议 #

• 匹配成功的行dest.testid和sou.userid本来就是相等的，无需执行UPDATE操作，可以直接删除WHEN MATCHED段逻辑，减少不必要的数据库写入开销。
• 建议将@sid的类型和test_master表的sid字段类型保持一致，避免隐式转换导致匹配失效，引发误删风险。

内容的提问来源于stack exchange，提问作者R.D Student