How to use MERGE to update only changed columns?

Question

I have this query which syncs the target table with the source table. Missing rows in the target need to be inserted, changed rows (only number and name) need to be updated, and missing rows in the source need to be deleted from the target table:

MERGE Table1 AS Target
USING Table2 AS Source
    ON Source.id_no = Target.Id
    AND Source.number = Target.Number
    AND Source.[name] = Target.[Name]
WHEN NOT MATCHED BY Target THEN
    INSERT (Id, Number, [Name]) 
    VALUES (Source.id_no, Source.number, Source.[name])
WHEN MATCHED THEN UPDATE SET
    Target.Number = Source.number,
    Target.[Name] = Source.[name]
WHEN NOT MATCHED BY Source THEN
    DELETE

But the above is always updating ALL rows every time the query is executed.

What am I doing wrong?

Answer 1

You need to change

USING Table2 AS Source
    ON Source.id_no = Target.Id
    AND Source.number = Target.Number
    AND Source.[name] = Target.[Name]

To just

USING Table2 AS Source
    ON Source.id_no = Target.Id

Otherwise if, for example, a row exists on both sides and has identical id and name but different Number then you will go through to the NOT MATCHED BY Target and NOT MATCHED BY Source clauses and never get through to the UPDATE branch anyway.

With that change in place you can use

WHEN MATCHED AND Target.Number IS DISTINCT FROM Source.number OR
                 Target.[Name] IS DISTINCT FROM  Source.[name] THEN UPDATE ...

To compare the "before" and "after" column values and only update if something changed.

WHEN MATCHED AND EXISTS (SELECT Target.Number, Target.[Name] 
                        EXCEPT 
                        SELECT Source.number, Source.[name]) THEN UPDATE ...

Is more concise if you have many columns to compare