Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2007
    Posts
    1

    Unanswered: deleting duplicate records

    hi,

    i need help. I'm new to db manipulation.
    My problem is on deleting duplicate records in a postgresql table.
    From the example below, I need to delete id_2 from table_a but before doing that
    id_2 from table_b and table_c should be changed to id_1. thanks in advance..

    table_a: id is primary key
    id | name | country
    ---------------------
    id_1 | kay | canada
    id_2 | kay | canada
    id_3 | kay | us


    table_b : id is foreign key ref. id.table_a
    id | status
    -------------
    id_2 | active

    table_c : id is foreign key ref. id.table_a
    id | course
    -------------
    id_2 | bscs

    desired output:
    table_a: id is primary key
    id | name | country
    ---------------------
    id_1 | kay | canada
    id_3 | kay | us


    table_b : id is foreign key ref. id.table_a
    id | status
    -------------
    id_1 | active

    table_c : id is foreign key ref. id.table_a
    id | course
    -------------
    id_1 | bscs

  2. #2
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    you could do this if you have table_b and table_c set to cascade their keys:

    delete from table_a where id = 'id_1';
    update table_a set id = 'id_1' where id = 'id_2';

    this will just delete the id_1 (duplicate) and turn id_2 into id_1.

    but this is only good if the foreign keys are using ON UPDATE CASCADE.

    You could update the keys manually hehe. bleh.

Posting Permissions

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