Any Elegant Ways to Merge RI References?

[glenn_barber]

We use Declarative RI throughout our application and it has the benefit of tying together customer accounts and their related transactions. However there are cases where we find that there have been inadvertent duplicate account entires and we want to merge all the references from the duplicates to a single account reference and eliminate the duplicate accounts.

So while we can change all references to an account by cascade update from the account, we cant merge references because of the duplication.

Are their any elegant ways to utilize existing RI to remap references, or is it pretty much brute force SQL updates on all the referencing tables?

[Former Member]

This is certainly a big issue that I have to deal with. Most of our apps work with Breck's simple update statement below. But I am dying to see more dialogue and hoping to glean to way better ideas from tis conversation. Please do follow up Glenn with more specifics. You will no doubt help a number of us.

[VolkerBarth]

@Bill: Feel free to ask your particular question then:)

Are you thinking of issues like the following:

• A customer may have 0..n phone numbers (as child records, apparently).
• Now you notice a customer duplicate and want to merge the phone numbers and want to prevent duplicate phone numbers?

That would be a situation where a simple cascading "fix the FK" rule over the parent records would not help... (and it would be one where "uniqueness" vs. duplicate are not too easy to define:)

[Former Member]

Hi Volker. I didn't have a particular question. After 17 years of working with SQL, I sometimes find amazingly simple techniques from the folks on this forum to replace my complicated and complex approach. So just hoping for one of those magical moments... 🙂

[Breck_Carter]

Is this what you are trying to do?

UPDATE child_table
   SET child_table.foreign_key = 'correct parent_table.primary_key value'
 WHERE child_table.foreign_key = 'incorrect parent_table.primary_key value';

I don't know if that qualifies as "brute force" since a single UPDATE takes care of all transactions for a single account. However, if you have hundreds or thousands of duplicated accounts to deal with, then perhaps a fancier solution would be worth constructing.

[VolkerBarth]

I guess it's more than that: If my understanding is correct, then the child_table might have duplicate values itself, i.e. the above UPDATE statement would lead to the situation where afterwards there are duplicate references to the same (merged) account.

Therefore one would need some statement with

a) a branch that updates references to the duplicate account not yet existing for the correct account, so that they point to the correct account) and b) a branch that deletes references to the duplicate account that already exist for the correct account.

That sounds pretty much like a perfect case for the MERGE statement with branches WHEN MATCHED AND <duplicate does exist> THEN DELETE WHEN MATCHED AND <duplicate does not exist> THEN UPDATE <the reference to the correct account>...

However, not knowing what makes a "reference a duplicate", I feel unable to show a small sample.

[Breck_Carter]

Glenn Barber mentioned only duplicate accounts (parents), not duplicate transactions (children)... however, as you point out, some clarification from Glenn would be helpful... as always, actual code, actual data 🙂

[VolkerBarth]

we can't merge references because of the duplication

That's what made me think of duplicate children - but as you say, wild-guessing is not that helpful:)