@r937 > I think he's asking about how to design a UI for performing the record merge - not the actual database queries involved. With that in mind...
Create a button called "Merge" and display it to the users with permission to use it on each record. When clicked prompt the user on the next screen to select the record(s) to 'merge into' it. When they click ok on that screen present a third screen that shows all of the records and asks for confirmation.
At this point you have a Primary record and a group of targets. The information of the primary over rides the targets if there is a conflict. The basic process at this point is to change the foreign keys of the child records of the targets to the id of the primary record that will be receiving the merges.
The database structure will obviously influence this greatly - and this will be much easier if the database is more or less hierarchical in structure - that is you have parent records with children. In this case you just need to reassign some foreign keys and call it a day.
I have a patient record system with a merge feature for patient records so for an example I'll walk over how I do it in that system. Patient records have addresses, phone numbers and "episodes of care" for their main child records. The episode of care has a claim table for a child, which in turn has bills, which in turn has procedures and in turn has ledgers items. Ledger items don't have a patient key - to find the patient of a ledger record you have to walk up the tree. That said, it makes merging easier because the number of tables referencing the patient record is minimal.
When merging patients I throw a warning if the SSN numbers do not match (Note, I would have primary keyed on SSN, but HIPPA law requires that the SSN be encrypted and working with encrypted keys is not ideal, further we don't always have the SSN - we usually do but not always). If the merge goes through the addresses are compared with exact matches discarded. If there's a slight misspelling the user will have to delete the redundant address entry manually. We already have to store multiple addresses since historical addresses must be submitted for historical claims even if the present address is different, so the patient having multiple addresses isn't much of a factor (ditto phone numbers). The primary address and phone is still set by the Primary record of the merge.
I hope this helps.