How to use MERGE to update only changed columns?

June 17, 2023

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?

>Solution :

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