Merge: when not matched by source - update rows

Question

I'm trying to figure out the merge function where I have to update rows that exist in target table but those rows doesn't match with rows in source table.

Those that didn't match in source table I would like to update in line where it say's WHEN NOT MATCHED BY SOURCE with something like
Update PEOPLE set UPD = null,target.CHANGE = CURRENT_TIMESTAMP where target.ID = source.ID and target.UPD is not null and target.CHANGE is null

This is what at the moment MERGE looks like, I think I need some output that will give me ID's that didn't match so I can update them but not sure how.

For example this are the rows in PEOPLE:

ID is not unique it is ID of a group

ID,NAME,SURNAME,UPD,CHECKED

4345,JOHN,DOE,1 - this one doesn't exist(his ID doesn't exist) in '#PEOPLE because it is insert from an earlier merge from an earlier #PEOPLE that now has changed

879,MARY,HON,1 - this one exist in #PEOPLE

9875,CHRISTIAN,TROY,1 - this one doesn't match the row's but his ID exist in PEOPLE and #PEOPLE

So from this list I want that JOHN DOE stay's as it is because his ID doesn't exit in #PEOPLE and CHRISTIAN TROY gets update where UPD will be NULL and CHANGE = CURRENT_TIMESTAMP but only if the UPD is not null and CHANGE is null because his ID exist in PEOPLE and #PEOPLE but entire row doesn't match.

MERGE INTO PEOPLE WITH (HOLDLOCK) AS target
USING #PEOPLE AS source

 on isnull(target.ID,'') = isnull(source.ID,'') 
 and isnull(target.NAME,'') = isnull(source.NAME,'') 
 and isnull(target.SURNAME,'') = isnull(source.SURNAME,'') 

WHEN MATCHED THEN 
    UPDATE SET target.UPD = 1
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID,NAME,SURNAME,UPD) 
    VALUES (source.ID ,source.NAME ,source.SURNAME,1)

WHEN NOT MATCHED BY SOURCE and target.UPD is not null and target.CHANGE is null THEN
  update set UPD = NULL,target.CHANGE  = CURRENT_TIMESTAMP

Any ideas?

Merge: when not matched by source - update rows

1 Answers