合并：当源表没有匹配时 - 更新行

Question

合并：当源表没有匹配时 - 更新行

3

我正在尝试理解合并函数，其中我必须更新目标表中存在但与源表中的行不匹配的行。

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

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Marine Fighter · Accepted Answer

阅读所有内容有点困难，但我能看到你的表中没有外键，这就是为什么你必须在合并后进行更新的原因。

你可以使用OUTPUT，但最简单的方法可能是像这样进行更新。如果匹配了UPD = 2，如果插入了UPD = 3..所以现在你有一个UPD = 1，那些不匹配的，所以你可以看到#People中的ID，并更新它们：

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);