How to delete duplicate rows in a table in sql\oracle ?

Question

How to delete duplicate rows in a table in sql\oracle ?

Answer 1

Oracle has a unique (hidden, but selectable) column called 'rowid'. This uniquely identifies each row within a table. Beware if you are in a clustered environment, however, as rowids can be duplicated in a cluster.



With this, there are two quick and easy ways to get rid of your dups:



1. Do this:



create table new_table as select distinct * from dups_table;

drop dubs_table;

rename new_table to dups_table;



... OR ...



2. Do this:



delete from dups_table dt

where exists

(select 'x' from dups_table dt2 where dt2.key_value1 = dt.key_value1 and dt2.key_value2 = dt.key_value2 and dt2.rowid != dt.rowid);





After you get the dups removed, be sure to add a Primary Key to the table and create unique indexes for fields within that table where appropriate so you won't run into the problem again.



Good luck, and IM/email with questions!

Answer 2

Oracle Delete Duplicate Rows

Answer 3

Delete Duplicate Rows In Oracle

Answer 4

Do you want to delete both duplicate rows, or just one?



Either way you are going to need to find a unique method of identifying multiple copies of the records since there are no unique key contraints applicable (otherwise you wouldn't have duplicate records). If there is a single unique field you could use something like:



delete from table where uniquefield in (select uniquefield from (select uniquefield, count(uniquefield) as ct from table where ct >= 2))



This will delete all instances of duplicates, leaving only records that did not originally have a duplicate record.



Otherwise, if it were me, I'd write a utility (in C#, VB, or whatever) to open a "live" dataset and start looping through records, deleteing duplicates along the way, especially if I wanted to keep at least one of the duplicated records.



After you've done this cleanup, I'd recommend adding a unique constraint to prevent these duplicates from being added in the future.

Answer 5

Duplicate rows means failure of data integraity and it happens in very old versions. Latest databases are very robust and it can not happen.