Urgent!!!- Replaceing data that repeats

[heihojin]

I need help with the sql statement to do the following - 2 tables are linked-contacttbl & addrtbl. The contact table contains a unique uid yet duplicate names. The addr table contains the contact-uid as foreign key. I know how to delete duplicate records. What I need help with is - I need to replace the contact-uid in the addr table with just one contact-uid after all the duplicates are deleted.
Before-
contacttbl addrtbl
contact-uid name addr-uid contact-uid addr
1 john smith 1 1 a
2 john smith 2 2 b
3 john smith 3 3 c
4 dave blue 4 4 x
After
contacttbl addrtbl
contact-uid name addr-uid contact-uid addr
1 john smith 1 1 a
4 dave blue 2 1 b
3 1 c
4 4 x
I appreciate any help. Thanks

 

[carp]

If your tables are properly normalized, you need to do the update BEFORE the deletion. Otherwise, there's no way to tie the child back to the parent.

I would recommend you go through the duplicates in your parent table, determine which of the duplicates is the REAL parent row, update all of the children to point to the real parent, and THEN delete the duplicates.

 

[heihojin]

How do I point the children records to the correct parent once the parent key is changed?

 

[carp]

This seems like something that would be better done via a PL/SQL script. The approach would be something like

For each group of duplicate rows LOOP
1. Decide which of the duplicates of a given type
(e.g., name = 'heihojin') is going to be the real
parent. Save that row's ID in a variable.

2. In the second table, update the foreign key values
for all children that point to a 'heihojin' parent so
that they have the value saved for the real parent.

3. Delete all of the 'heihojin' parent rows that are
not the designated "real" heihojin.
END LOOP;