如何在MERGE语句的DELETE子句中实现带用户匹配的条件删除？

要实现仅删除源数据（CTE）中存在的用户的未匹配映射，你需要在WHEN NOT MATCHED BY SOURCE子句中添加条件，限定仅处理CTE中包含的用户ID。由于WHEN NOT MATCHED BY SOURCE对应的是源中不存在匹配行的目标行，无法直接引用CTE的列，但可以通过子查询检查目标行的用户是否存在于CTE中。

修改后的MERGE语句 #

将WHEN NOT MATCHED BY SOURCE部分的条件调整为：

WHEN NOT MATCHED BY SOURCE
    AND A.MUG_FromSAML = 1
    AND A.MUG_USR IN (SELECT MUG_USR FROM CTE) -- 仅处理CTE中存在的用户
THEN DELETE

完整的MERGE代码如下：

;WITH CTE AS 
( 
    SELECT 
         MUG_UID 
        ,MUG_USR 
        ,MUG_Group 
        ,MUG_FromSAML 
   FROM 
   (        
        SELECT 
             NEWID() AS MUG_UID 
            ,1 AS MUG_USR 
            ,7000 AS MUG_Group 
            ,1 AS MUG_FromSAML 

        UNION 

        SELECT 
             NEWID() AS MUG_UID 
            ,33 AS MUG_USR 
            ,33000 AS MUG_Group 
            ,1 AS MUG_FromSAML 
   ) AS tSource 
) 
MERGE INTO dbo.Map_User_Groups  AS A 
USING CTE ON A.MUG_USR = CTE.MUG_USR AND A.MUG_Group = CTE.MUG_Group 
WHEN MATCHED THEN 
    UPDATE 
        SET A.MUG_FromSAML = CTE.MUG_FromSAML 

WHEN NOT MATCHED BY TARGET THEN 
    INSERT 
    ( 
         MUG_UID 
        ,MUG_USR 
        ,MUG_Group 
        ,MUG_FromSAML 
    ) 
    VALUES 
    ( 
         CTE.MUG_UID 
        ,CTE.MUG_USR 
        ,CTE.MUG_Group 
        ,CTE.MUG_FromSAML 
    ) 
WHEN NOT MATCHED BY SOURCE
    AND A.MUG_FromSAML = 1
    AND A.MUG_USR IN (SELECT MUG_USR FROM CTE) -- 关键条件：仅处理CTE中的用户
THEN DELETE 
;

逻辑说明 #

1. WHEN NOT MATCHED BY SOURCE：匹配目标表中存在但源数据（CTE）中没有对应（用户+组）组合的行。
2. AND A.MUG_USR IN (SELECT MUG_USR FROM CTE)：进一步限定仅处理CTE中包含的用户，彻底避免误删其他用户（如示例中的用户2、3）的映射。
3. AND A.MUG_FromSAML =1：保留你原本的过滤条件，仅删除来自SAML的映射。

这样既满足了你的核心需求——无需手动指定单个用户ID、不重复查询逻辑、不使用单独的DELETE语句，又能严格保证不会误删无关用户的映射。

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