Merge when not matched by source without deleting other rows

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:

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

• The WHEN MATCHED AND target.default_skill <> source.default_skill THEN condition will check if the default_skill values do not match between the source and target tables for matched rows. In that case, it performs an update to set the default_skill value in the target table to match the source table.

• The WHEN NOT MATCHED BY SOURCE THEN DELETE condition remains the same : it will delete rows from the target table that do not have a match in the source table.

MERGE appuser_skills AS target
USING 
(
    SELECT
        userid,
        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 AND target.skill_id = source.skill_id)
WHEN MATCHED AND target.default_skill <> source.default_skill THEN
    UPDATE SET target.default_skill = source.default_skill
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

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.