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;
Sunday, May 11, 2008
How to eliminate duplicate rows from a table in Oracle?
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment