Sunday, May 11, 2008

How to eliminate duplicate rows from a table in Oracle?

First of all we need to create a test table called test_duplicates.
SQL>create table test_duplicates (emp_id char(10), emp_name char(50), emp_desg char(25));


Now we insert data in to this table by following dml statements.
SQL>insert into test_duplicates values ('01','Arun','DBA');
SQL>insert into test_duplicates values ('01','Vicky','DBA');

SQL>insert into test_duplicates values ('02','Krutin','VoIP');
SQL>insert into test_duplicates values ('03','Pratik','.Net');
SQL>insert into test_duplicates values ('01','Arun','DBA');

Now we want to check that how many record having same emp_id.
select a.* from test_duplicates a where rowid > (select min(rowid) from test_duplicates b where a.EMP_ID = b.emp_id )

Now we want to delete those duplicate records.

delete from test_duplicates a where rowid > (select min(rowid) from test_duplicates b where a.EMP_ID = b.emp_id) ;

Wind up all the things.

drop table test_duplicates purge;

No comments: