Merge when not matched by source without deleting other rows

Question

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:

Userid	skill_id	default_skill
1132	421	0
1132	2160	1
1131	789	1

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

Answer 1

I noticed you also want an insert. I don't think all those can be solved by merge. I think it's time to do what you should've done from the beginning, don't use merge, or at least use several statements:

-- Create test data
drop table #source
drop table #target
SELECt  *
into #source
FROM    (
    VALUES  (1132, 2160, 1)
    ,   (1132, 666, 0)
) t (Userid,skill_id,default_skill)

select *
into #target
FROM    (
    VALUES  (1132, 421, 0)
    ,   (1132, 2160, 0)
    ,   (1131, 789, 1)
    ) t (Userid,skill_id,default_skill)


-- The actual merge
;WITH target AS (
    select *
    from #target t -- We work only with users in the source table
    WHERE   EXISTS(
            SELECT  1
            FROM    #source s
            WHERE   s.Userid = t.UserId
    )
)
merge target AS t
USING #source s
    ON  s.UserID = t.UserID
    AND s.skill_id = t.skill_id
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT (UserId, skill_id, default_skill)
VALUES (s.UserId, s.skill_id, s.default_skill)
WHEN MATCHED and s.default_skill <> t.default_skill THEN UPDATE
    SET default_skill = s.default_skill
output inserted.*, deleted.*, $action
;
-- Separate insert of completely new users
insert into #target (
    Userid, skill_id, default_skill
)
select  Userid, skill_id, default_skill
FROm    #source s
WHERE   NOT EXISTS(
        SELECT  1
        FROM    #target t
        WHERE   t.Userid = s.userId
        --AND   t.skill_id = s.skill_id -- Shouldn't be needed
    )

select *
from #source
select *
from #target

I choose to split out the INSERT of completely new users, since it's usually the easiest to check using WHERE NOT EXISTS.

For the delete, i make sure target rows participating in the merge only comes from users in the #source-table.