避免SQL Merge Update语句上的密钥冲突

时间：2022-11-17 19:16:15

I have a table with two columns, Prime_GuestID and Dup_GuestID to indicate the links between an GuestID's and the ID(s) it is replacing (duplicate records)

我有一个包含两列Prime_GuestID和Dup_GuestID的表来指示GuestID和它正在替换的ID之间的链接（重复记录）

Now I want to go through a number of other relationship tables of the format and update any occurrences of a Dup_GuestID to it's Prime_GuestID.

现在我想通过格式的许多其他关系表，并将Dup_GuestID的任何出现更新为Prime_GuestID。

However if the Prime_GuestID already has an entry for a given ThingID then instead I need to delete that row from the relationship table.

但是，如果Prime_GuestID已经有给定ThingID的条目，那么我需要从关系表中删除该行。

Currently I'm using the following script, though while it works for most cases it fails if two Dup_GuestID's update to the same Prime_GuestID for a given ThingID. It seems like the merge statement queues up all the changes before making them, therefore my * check won't detect them.

目前我正在使用以下脚本，虽然它适用于大多数情况但如果两个Dup_GuestID更新为给定ThingID的相同Prime_GuestID，则会失败。似乎merge语句在进行之前将所有更改排队，因此我的冲突检查不会检测到它们。

MERGE Thing_Relation AS t
USING Guest_Relation AS g
    ON t.GuestID = g.Dup_GuestID
WHEN MATCHED AND EXISTS (  -- * Check here
                        select * 
                        from Thing_Relation AS t2 
                        where t2.ThingID = t.ThingID 
                        and t2.GuestID = g.Prime_GuestID
                        ) 
 THEN DELETE
WHEN MATCHED 
 THEN UPDATE SET t.GuestID = g.Prime_GuestID

Is there a better way to be doing the check at 'When matched and exists' to check for *es that would result from this merge? Or is there a better way to do this whole thing?

有没有更好的方法在'匹配和存在时'进行检查以检查由此合并产生的冲突？或者有更好的方法来做这整件事吗？

EDIT: Here is some sample data for the table

编辑：这是该表的一些示例数据

Thing_Relation          Guest_Relation
ThingID | GuestID       Prime_GuestID | Dup_GuestID
------------------      ---------------------------
1       | 101                     101 | 102
1       | 102                     107 | 104
2       | 103                     107 | 105
3       | 104
3       | 105

Thing_Relation after merge
ThingID | GuestID       
------------------      
1       | 101              
2       | 103
3       | 107

The 1|102 gets changed to 1|101 which already exists so row is deleted. 2|103 isn't affected 3|104 is changed to 3|107, and since 3|105 also changes to 3|107 but the previous update hasn't happened yet it isn't picked up by the EXISTS clause.

1 | 102变为1 | 101已经存在，因此删除了行。 2 | 103不受影响3 | 104更改为3 | 107，并且由于3 | 105也更改为3 | 107，但之前的更新尚未发生，因此EXISTS子句未接收到它。

1 个解决方案

#1

0  

You are right, MERGE would not run your checks on the changes made by the MERGE statement itself. It is the basic property of MERGE. Few of the options could be :

你是对的，MERGE不会对MERGE语句本身所做的更改进行检查。这是MERGE的基本属性。几个选项可能是：

  1. Create a TRIGGER on UPDATE that will keep on checking for *es on every update and delete the duplicate rows. OR
  2. 在UPDATE上创建一个TRIGGER，它将继续检查每次更新时的冲突并删除重复的行。要么
  3. simply write two different statements one for UPDATE, later one DELETE for duplicate entries.
  4. 只需为UPDATE写一个不同的语句，然后为重复的条目写一个DELETE。

#1

0  

You are right, MERGE would not run your checks on the changes made by the MERGE statement itself. It is the basic property of MERGE. Few of the options could be :

你是对的，MERGE不会对MERGE语句本身所做的更改进行检查。这是MERGE的基本属性。几个选项可能是：

  1. Create a TRIGGER on UPDATE that will keep on checking for *es on every update and delete the duplicate rows. OR
  2. 在UPDATE上创建一个TRIGGER，它将继续检查每次更新时的冲突并删除重复的行。要么
  3. simply write two different statements one for UPDATE, later one DELETE for duplicate entries.
  4. 只需为UPDATE写一个不同的语句，然后为重复的条目写一个DELETE。
标签：

相关文章