How do I delete only related records in a multi-key MERGE in SQL Server? Suppose you have something like this:

Source table (variable):

Values (
  LeftId INT NOT NULL,
  RightId INT NOT NULL,
  CustomValue varchar(100) NULL
)


Target table: 

Mapping (
  LeftId INT NOT NULL,
  RightId INT NOT NULL,
  CustomValue varchar(100) NULL
)


I want to merge Values into Target with the following rules:


Match on source.LeftId = target.LeftId AND source.RightId = target.RightId

when matched in target, update CustomValue
when not matched in target, insert

Delete any unmatched values in the target that do match a LeftId in the source, i.e. only delete records that related to the LefIds of what I'm merging.


(That last rule is hard to describe, sorry!)

For instance:

Source: 

1, 10, foo
1, 11, foo


Target:

1, 10, bar
1, 12, foo
2, 20, car


Merge result:

Result Target: 

1, 10, foo (updated)
1, 11, foo (inserted)
1, 12, foo (deleted)
2, 20, car (unchanged)


So...

Here's what I have so far, which takes care of update and insert:

MERGE Mapping AS target
USING (SELECT LeftId, RightId, CustomValue FROM @Values) 
  AS source (LeftId, RightId, CustomValue)
  ON target.LeftId = source.LeftId
    AND target.RightId = source.RightId
WHEN NOT MATCHED THEN
  INSERT (LeftId, RightId, CustomValue)
  VALUES (source.LeftId, source.RightId, source.CustomValue)
WHEN MATCHED THEN
  UPDATE SET
    CustomValue = source.CustomValue;


How do I do the delete part of my rule?
You can filter out the rows you need to consider from the target table in a CTE and use the CTE as the target in the merge.

WITH T AS
(
  SELECT M.LeftId, 
         M.RightId, 
         M.CustomValue
  FROM @Mappings AS M
  WHERE EXISTS (SELECT *
                FROM @Values AS V
                WHERE M.LeftId = V.LeftId) 
)
MERGE T
USING @Values AS S
ON T.LeftId = S.LeftId and
   T.RightId = S.RightId
WHEN NOT MATCHED BY TARGET THEN
  INSERT (LeftId, RightId, CustomValue) 
  VALUES (S.LeftId, S.RightId, S.CustomValue)
WHEN MATCHED THEN
  UPDATE SET CustomValue = S.CustomValue
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
;