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
;

StackExchange Database Administrators Q#48055, answer score: 10