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?

asked 05 Jan '14, 15:07

Glenn%20Barber's gravatar image

Glenn Barber
1.1k274456
accept rate: 8%

edited 11 Jan '14, 07:59

Graeme%20Perrow's gravatar image

Graeme Perrow
9.6k379124

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.

(06 Jan '14, 10:15) Bill Aumen
Replies hidden

@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:)

(06 Jan '14, 10:28) Volker Barth

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... :)

(09 Jan '14, 11:14) Bill Aumen

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.

permanent link

answered 05 Jan '14, 16:46

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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.

(06 Jan '14, 03:11) Volker Barth
Replies hidden

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

(06 Jan '14, 07:43) Breck Carter

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

(06 Jan '14, 07:58) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×4

question asked: 05 Jan '14, 15:07

question was seen: 1,531 times

last updated: 11 Jan '14, 07:59