Total Pageviews

August 10, 2015

8/10/2015 10:23:00 AM

How can I eliminate duplicates values in a table?
Choose one of the following queries to identify or remove duplicate rows from a table leaving one record:
Method 1:
   SQL> DELETE FROM table_name A WHERE ROWID > (
     2    SELECT min(rowid) FROM table_name B
     3    WHERE A.key_values = B.key_values);
Method 2:
   SQL> create table table_name2 as select distinct * from table_name1;
   SQL> drop table_name1;
   SQL> rename table_name2 to table_name1;
Method 3:
   SQL> Delete from my_table where rowid not in(
   SQL>     select max(rowid) from my_table
   SQL>     group by my_column_name );
Method 4:
   SQL> delete from my_table t1
   SQL> where  exists (select 'x' from my_table t2
   SQL>                 where t2.key_value1 = t1.key_value1
   SQL>                   and t2.key_value2 = t1.key_value2
   SQL>                   and t2.rowid      > t1.rowid);
Note: If you create an index on the joined fields in the inner loop, you for all intensive purposes eliminate N^2 operations (no need to loop through the entire table on each pass by a record).

Related Posts Plugin for WordPress, Blogger...