Thanks for the script, but I don't think your description is quite right:

What is actually happening is that (1, 'A') is not matched by the source in @T2 so it is deleted, and then (1, 'AA') and (3, 'C') in the source are not matched in the target, so they are inserted.

It seems that (1, 'A') has been updated, but actually it has been deleted, and then (1, 'AA') inserted.

So the matching rules are being followed exactly as I would expect. It's just that the logic needs to be carefully understood when using "WHEN NOT MATCHED BY SOURCE" and "WHEN NOT MATCHED BY TARGET" rules in a statement that uses a non-trivial search condition.

This behaviour can be seen by adding an OUTPUT clause to your Merge:

SET NOCOUNT ON;

USE tempdb;

GO

DECLARE @T1 TABLE (

c1 int NOT NULL PRIMARY KEY,

c2 varchar(15) NOT NULL

);

DECLARE @T2 TABLE (

c1 int NOT NULL PRIMARY KEY,

c2 varchar(15) NOT NULL UNIQUE

);

INSERT INTO @T1 (c1, c2)

VALUES (1, 'A'), (2, 'C');

INSERT INTO @T2 (c1, c2)

VALUES (1, 'AA'), (2, 'B'), (3, 'C');

SELECT * FROM @T1;

SELECT * FROM @T2;

MERGE INTO @T1 AS T1

USING @T2 AS T2

ON T1.c1 = T2.c1 AND T1.c2 = 'C'

WHEN MATCHED THEN

UPDATE SET T1.c2 = T2.c2

WHEN NOT MATCHED BY SOURCE THEN

DELETE

WHEN NOT MATCHED BY TARGET THEN

INSERT (c1, c2) VALUES (T2.c1, T2.c2)

OUTPUT $action,

deleted.c1 as deleted_c1, deleted.c2 as deleted_c2,

inserted.c1 as inserted_c1, inserted.c2 as inserted_c2

;

SELECT * FROM @T1;

SELECT * FROM @T2;

Produces this OUTPUT from the Merge:

$action deleted_c1 deleted_c2 inserted_c1 inserted_c2

DELETE 1 A NULL NULL

INSERT NULL NULL 1 AA

UPDATE 2 C 2 B

INSERT NULL NULL 3 C