3

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?

Dragon.M
  • 249
  • 1
  • 4
  • 11

1 Answers1

2

It was a bit hard to read all of this but as much as I can see you don't have any FK in your table and that is why you have to make update after merge.

You could use OUTPUT but maybe the easiest way would be to make updates like this. if matched UPD=2 and if it is and insert UPD = 3.. So now you have on UPD = 1 those that are not matched so you see what are ID's in #People and you update them:

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 = 2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID,NAME,SURNAME,UPD) 
    VALUES (source.ID ,source.NAME ,source.SURNAME,3)

;

UPDATE PEOPLE set UPD = null,CHANGE = CURRENT_TIMESTAMP where UPD = 1 and CHANGE is null and ID in (Select distinct ID from #PEOPLE);
UPDATE PEOPLE set UPD = 1 where (UPD =2 or UPD=3) and MB in (Select distinct MB from #PEOPLE);
Marine Fighter
  • 383
  • 2
  • 7