Replace data in child table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Replace data in child table

  1. #1
    Join Date
    Apr 2003
    Posts
    1

    Question Replace data in child table

    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Let's say that from all the duplicates in contact table, we'll leave only those with the lowest ROWIDs.

    So first, let's switch the records in address table that belong to duplicates, to the ones that will remain in contact table:
    Code:
    UPDATE addrtbl SET contact_uid = 
      (SELECT contact_uid FROM contacttbl c1
       WHERE name = (SELECT name FROM contacttbl c2
                     WHERE c2.contact_uid = addrtbl.contact_uid)
       AND NOT EXISTS (SELECT null FROM contacttbl c3
                       WHERE c3.rowid < c1.rowid)
      )
    WHERE EXISTS (SELECT null FROM contacttbl c4
                  WHERE contact_uid = addrtbl.contact_uid
                  AND EXISTS (SELECT null FROM contacttbl c5
                              WHERE c4.name = c5.name
                              AND c5.rowid > c4.rowid));
    And finaly, let's delete the duplicates from the contact table:
    Code:
    DELETE FROM contacttbl c1
    WHERE EXISTS (SELECT null FROM contacttbl c2
                  WHERE c2.name = c1.name
                  AND c2.rowid < c1.rowid);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width