sql server - Avoid key clash on SQL Merge Update statement -

i have table 2 columns, prime_guestid , dup_guestid indicate links between guestid's , id(s) replacing (duplicate records)

now want go through number of other relationship tables of format , update occurrences of dup_guestid it's prime_guestid.

however if prime_guestid has entry given thingid instead need delete row relationship table.

currently i'm using following script, though while works cases fails if 2 dup_guestid's update same prime_guestid given thingid. seems merge statement queues changes before making them, therefore clash check won't detect them.

merge thing_relation t using guest_relation g     on t.guestid = g.dup_guestid when matched , exists (  -- clash check here                         select *                          thing_relation t2                          t2.thingid = t.thingid                          , t2.guestid = g.prime_guestid                         )   delete when matched   update set t.guestid = g.prime_guestid 

is there better way doing check @ 'when matched , exists' check clashes result merge? or there better way whole thing?

edit: here sample data 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 1|101 exists row deleted. 2|103 isn't affected 3|104 changed 3|107, , since 3|105 changes 3|107 previous update hasn't happened yet isn't picked exists clause.

you right, merge not run checks on changes made merge statement itself. basic property of merge. few of options :

1. create trigger on update keep on checking clashes on every update , delete duplicate rows. or
2. simply write 2 different statements 1 update, later 1 delete duplicate entries.