affiliate marketing Interview Qns: Eliminate duplicate rows
Showing posts with label Eliminate duplicate rows. Show all posts
Showing posts with label Eliminate duplicate rows. Show all posts

Monday, October 25, 2010

Can I remove duplicate rows ?

Yes, using the ROWID field. The ROWID is guaranteed unique. There are many variations on this theme, but the logic is to delete all but one record for each key value.
delete from EMP E
where not E.ROWID = (
select min(F.ROWID)
from EMP F
where F.EMP_ID = E.EMP_ID
);

How does one eliminate duplicates rows from a table?

Choose one of the following queries to identify or remove duplicate rows from a table leaving only unique records in the table:

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;

SQL> -- Remember to recreate all indexes, constraints, triggers, etc on table...

Method 3: (thanks to Dennis Gurnick)

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);