Reputation: 277
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?
Upvotes: 3
Views: 4778
Reputation: 403
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);
Upvotes: 2