I am trying to update a target table from a source table using merge. The source table does not have all the rows that the target table does, so when I do WHEN NOT MATCHED BY SOURCE , all the target rows not in my source table get deleted. I only want to delete specific ones.

So for example I have this in my source table:
| Userid | skill_id | default_skill |
| ------------ | ------------ | ------------ |
| 1132 | 2160 | 1 |

My target table has many rows, but here is an example:

If I simple do WHEN NOT MATCHED BY SOURCE THEN Delete , it will delete 1131 userid from the target table because it is not in the source. I only want to delete the one from the target table that doesn't match the source. So it would delete the |1132|421|0| row.

Would I merge on the userid then when matched and the skill_ids don't match, delete? But what if I need a when matched but they do match and I just want to update the default_skill column. I can't have multiple WHEN MATCH can I?

This is my current merge that I am trying to do it in:

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
        );