Dwain Camps posted an article about using CTE to limit Target table when we apply MERGE.
It is a very good article to raise the issue. There is another option to limit the target table for this condition: WHEN NOT MATCHED BY SOURCE with DELETE
with an AND condition to limit the target table rows and also we have another option and the last option to use the condition with UPDATE.
You can find original sample in the article from above link.
Here is the modified sample with the AND condition for DELETE and the operation of UPDATE for the second one:
CREATE TABLE #Test1 (ID INT, RowNo INT, Value decimal(16,2)); CREATE TABLE #Test2 (ID INT, RowNo INT, Value decimal(16,2)); -- target INSERT INTO #Test1 (ID, RowNo, Value) VALUES (1,1,25),(1,2,32),(2,1,38),(2,2,61),(2,4,43),(3,1,15),(3,2,99),(3,3,54); -- source INSERT INTO #Test2 (ID, RowNo, Value) VALUES (2,1,45),(2,2,88),(2,3,28); BEGIN TRANSACTION T1; MERGE #Test1 t -- Target USING #Test2 s -- Source ON t.ID = s.ID AND t.RowNo = s.RowNo WHEN MATCHED THEN UPDATE SET Value = s.Value WHEN NOT MATCHED -- Target THEN INSERT (ID, RowNo, Value) VALUES (s.ID, s.RowNo, s.Value) WHEN NOT MATCHED BY SOURCE And t.ID in (Select ID from #test2) --You cannot access the source ID here and instead we use a subquery THEN DELETE WHEN NOT MATCHED BY SOURCE THEN UPDATE SET Value = t.Value*100; SELECT * FROM #Test1 ORDER BY ID, RowNo; ROLLBACK TRANSACTION T1; drop table #Test1 drop table #Test2