Delete duplicates from a composite primary key table

Question

I have a table with composite primary key(a,b). I took a backup in temp table but some how there came the duplicate records. now am not able to move the backup records to main table.

kindly suggest some way to delete the duplicates in the temp table based on 2 columns. Table is oracle table with 70 million records around 4 million duplicates

Answer 1

You have two options:

1. delete the duplicates in the source table before copying them
2. don't copy the duplicates in the first place.

The second one is more efficient and can be done using this statement:

INSERT INTO target_table (a,b,c,d,e,f)
SELECT a,b,max(c),max(d),max(e),max(f)
FROM source_table
GROUP BY a,b;

As an alternative (e.g. when some of the columns are defined as CLOB or LONG and you can't apply a max() on them):

INSERT INTO target_table (a,b,c,d,e,f)
SELECT a,b,c,d,e,f
FROM (
    SELECT a,b,c,d,e,f,
           row_number() over (partition by a,b) as rn
    FROM source_table
) 
WHERE rn = 1;

If you really want to first delete the rows, you can do this with the following statement:

DELETE FROM source_table
WHERE rowid NOT IN (SELECT min(rowid)
                    FROM source_table
                    GROUP BY a,b
                    HAVING count(*) > 1)