我正在尝试理解合并函数,其中我必须更新目标表中存在但与源表中的行不匹配的行。
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?