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?
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.
answered 05 Jan '14, 16:46