Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    india, delhi
    Posts
    18

    Post Unanswered: deleting duplicate records in oracle

    Hi,

    I want to know whether there is some specific query to delete duplicate records from a table.. if yes please tell me... u can directly mail me at sudeesh@galoistech.com or sudeesh77@yahoo.com...

    Also how we can delete duplicate records..
    Please reply soon if any know this...

    Regards
    Sudeesh
    www.GaloisTech.com

  2. #2
    Join Date
    Oct 2003
    Posts
    87
    The problem is identifying which dup's to delete! Someone with expert knowledge of the data (all columns) will have to determine whichs dups to delete. If two or more rows exist where all columns are dup'ed you'll have to save an image of the row, delete them all, then insert the saved image. Once all dups are deleted, be sure to immediately create a unique index.
    Oracle - DB2 - MS Access -

  3. #3
    Join Date
    Oct 2003
    Location
    india, delhi
    Posts
    18

    need generalized query

    Originally posted by N-ary
    The problem is identifying which dup's to delete! Someone with expert knowledge of the data (all columns) will have to determine whichs dups to delete. If two or more rows exist where all columns are dup'ed you'll have to save an image of the row, delete them all, then insert the saved image. Once all dups are deleted, be sure to immediately create a unique index.

    Hi Buddy,

    Some oracle person said that there is some specific query...
    I ant a query whic can be applied to any table (like SELECT * FROM Table)...

    User doesn't know how many columns are there....

    rgds
    Sudeesh
    Sudeesh
    www.GaloisTech.com

  4. #4
    Join Date
    Nov 2002
    Posts
    833
    Rem Script to delete duplicate rows from a table
    Rem
    Rem Enter Table_Name as MY_TABLE
    Rem And Column_List as COLUMN_1,COLUMN_2,COLUMN_3 ... Column_N
    Rem (i.e. no spaces)
    Rem
    Rem
    Accept table_name Prompt 'Enter Table Name: '
    Accept column_list Prompt 'Enter Column List (no spaces): '
    BEGIN
    LOOP
    DELETE FROM &table_name
    WHERE ROWID IN (SELECT MIN (ROWID)
    FROM &table_name
    GROUP BY &column_list
    HAVING COUNT (*) > 1);
    EXIT WHEN SQL%NOTFOUND;
    END LOOP;
    COMMIT;
    END;
    /

  5. #5
    Join Date
    Oct 2003
    Location
    india, delhi
    Posts
    18

    Thumbs up

    Originally posted by osy45
    Rem Script to delete duplicate rows from a table
    Rem
    Rem Enter Table_Name as MY_TABLE
    Rem And Column_List as COLUMN_1,COLUMN_2,COLUMN_3 ... Column_N
    Rem (i.e. no spaces)
    Rem
    Rem
    Accept table_name Prompt 'Enter Table Name: '
    Accept column_list Prompt 'Enter Column List (no spaces): '
    BEGIN
    LOOP
    DELETE FROM &table_name
    WHERE ROWID IN (SELECT MIN (ROWID)
    FROM &table_name
    GROUP BY &column_list
    HAVING COUNT (*) > 1);
    EXIT WHEN SQL%NOTFOUND;
    END LOOP;
    COMMIT;
    END;
    /

    Hi osy,
    your solution is in pl/sql,
    but i want im sql only.. it can be done in pl/sql easily...

    can u please tell sql query..

    thanks
    Sudeesh
    www.GaloisTech.com

  6. #6
    Join Date
    Nov 2002
    Posts
    833
    DELETE FROM <table_name>
    WHERE ROWID IN (SELECT MIN (ROWID)
    FROM <table_name>
    GROUP BY col1, col2, col3, ... , col<n>
    HAVING COUNT (*) > 1);

  7. #7
    Join Date
    Oct 2003
    Posts
    87

    Re: need generalized query

    Originally posted by sudeesh
    Hi Buddy,

    Some oracle person said that there is some specific query...
    I ant a query whic can be applied to any table (like SELECT * FROM Table)...

    User doesn't know how many columns are there....

    rgds
    Sudeesh
    I guess I didn't make my point clear. Given the following, dup'ed on col1 and col2:
    a,b,c,d,e
    a,b,f,g,h
    a,b,i,j,k
    How do you know which two to delete? Simply deleting the two with the lower or higher rowid might not delete the correct dups.
    There is no query that can select the correct dup(s) to delete unless there is a non-key column(s) that can uniquely identify either the one to keep or the others to delete. Ex; col3 = 'f'.
    Oracle - DB2 - MS Access -

Posting Permissions

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