如何在MERGE语句中仅删除目标表指定不匹配行，保留其他行

使用MERGE语句从源表更新目标表时遇到问题：源表不包含目标表的所有行，若使用WHEN NOT MATCHED BY SOURCE THEN Delete会删除目标表中所有源表没有的行，但只想删除特定用户（比如示例中的Userid=1132）的多余技能行，而非其他用户的行。

示例数据：

• 源表：

• 目标表：

执行WHEN NOT MATCHED BY SOURCE THEN Delete会删除Userid=1131的行，但实际只想删除Userid=1132、skill_id=421的行。

疑问：是否应该仅按userid关联，然后在匹配但skill_id不匹配时执行删除？同时需要在匹配且skill_id相同时更新default_skill列，MERGE是否支持多个WHEN MATCHED子句？

当前使用的MERGE语句：

MERGE appuser_skills USING 
(
    SELECT
         userid,
         #tmp.username,
         s.value AS skill_id,
         CASE
            WHEN s.value = #tmp.primaryskillid THEN 1
            ELSE 0
        END AS default_skill
    FROM
        #tmp
        CROSS APPLY
        dbo.splitinteger(#tmp.skilllist,',') s
        JOIN skill WITH(NOLOCK) ON skill.skill_id = s.value
) a
ON appuser_skills.user_id = a.userid AND appuser_skills.skill_id = a.skill_id
WHEN MATCHED THEN
    UPDATE SET
        default_skill = CASE
                            WHEN a.default_skill = 1 THEN 1
                            ELSE 0
                        END
WHEN NOT MATCHED THEN
    INSERT
        (
            user_id,
            skill_id,
            weight,
            proficiency,
            default_skill
        )
    VALUES
        (
            (SELECT user_id FROM appuser WHERE appuser.username = a.username),
            a.skill_id,
            0,
            0,
            a.default_skill
        );

1. MERGE支持多个WHEN MATCHED子句 #

是的，MERGE允许定义多个WHEN MATCHED子句，每个子句可带独立条件判断，执行不同操作（更新或删除），但需注意子句顺序至关重要，SQL会按顺序匹配第一个满足条件的子句执行。

2. 实现需求的核心思路 #

要只删除源表中存在的用户的多余技能行，同时保留其他用户的所有数据，需调整关联逻辑和匹配分支：

• 仅按user_id进行关联，让源表存在的用户的所有目标表行进入匹配分支
• 第一个WHEN MATCHED子句处理skill_id相同的场景：更新default_skill列
• 第二个WHEN MATCHED子句处理同用户但skill_id不同的场景：删除该行
• 保留插入逻辑，添加源表有但目标表没有的技能行

3. 修改后的MERGE语句 #

MERGE appuser_skills AS target
USING (
    SELECT
         userid,
         #tmp.username,
         s.value AS skill_id,
         CASE
            WHEN s.value = #tmp.primaryskillid THEN 1
            ELSE 0
        END AS default_skill
    FROM
        #tmp
        CROSS APPLY dbo.splitinteger(#tmp.skilllist,',') s
        JOIN skill WITH(NOLOCK) ON skill.skill_id = s.value
) AS source
ON target.user_id = source.userid -- 仅按user_id关联
WHEN MATCHED AND target.skill_id = source.skill_id THEN -- 技能匹配时更新default_skill
    UPDATE SET default_skill = source.default_skill
WHEN MATCHED AND target.skill_id != source.skill_id THEN -- 同用户但技能不匹配时删除该行
    DELETE
WHEN NOT MATCHED BY TARGET THEN -- 插入源表存在但目标表缺失的技能行
    INSERT (user_id, skill_id, weight, proficiency, default_skill)
    VALUES (
        (SELECT user_id FROM appuser WHERE appuser.username = source.username),
        source.skill_id,
        0,
        0,
        source.default_skill
    );

4. 逻辑说明 #

• 关联仅匹配user_id，源表中存在的用户（如1132）的所有目标表行会进入匹配分支
• 技能ID匹配时，更新default_skill列
• 同用户但技能ID不匹配时，删除该行（即1132用户的skill_id=421行会被删除）
• 源表中不存在的用户（如1131）的目标表行不会进入任何匹配分支，因此会被完整保留
• 源表有但目标表没有的技能行，会被插入到目标表中

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