Google
Information Storage and Retrieval: How does one eliminate duplicate rows in an Oracle Table?

Pages

Tuesday, January 19, 2010

How does one eliminate duplicate rows in an Oracle Table?

Method 1:

DELETE from table_name A
where rowid > (select min(rowid) from table_name B where A.key_values = B.key_values);

Method 2:

create table table_name2 as select distinct * from table_name1;
drop table table_name1;
rename table table_name2 as table_name1;

In this method, all the indexes,constraints,triggers etc have to be re-created.

Method 3:

DELETE from table_name t1
where exists (select 'x' from table_name t2 where t1.key_value=t2.key_value
                      and t1.rowid > t2.rowid)

Method 4:

DELETE from table_name where rowid not in (select min(rowid) from my_table group by key_value )

1 comment:

Anonymous said...

There is a flaw in method 4, This method would fail if there are more than one duplicate rows.