How Delete duplicate rows from table in oracle

No.of Views3503
Bookmarked0 times
By  Nimish Garg   On  05 Mar 2011 05:03:42
Tag : Oracle , Database Administrator
In this snippet, i will show how to delete duplicate rows from oracle.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at



In this snippet, i will show how to delete duplicate rows  from oracle.

Lets say out table is

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                                NOT NULL             NUMBER(4)
 ENAME                                                                                 VARCHAR2(10)
 JOB                                                                                        VARCHAR2(9)
 MGR                                                                                      NUMBER(4)
 HIREDATE                                                                            DATE
 SAL                                                                                        NUMBER(7,2)
 COMM                                                                                  NUMBER(7,2)
 DEPTNO                                                                               NUMBER(2)

1. Simple & Easy Way

delete from emp 
where rowid 
not in (select max(rowid) from emp group by empno,ename,job,mgr,hiredate,sal,comm,deptno);

2. Optimized with outer Join

delete from emp where rowid in 
 select rid_d from 
 (select rowid rid_d from emp) a,
 (select max(rowid) rid from emp group by empno,ename,job,mgr,hiredate,sal,comm,deptno) b
 where rid_d = rid (+)
 and rid is null

3. Alternate way to delete duplicate with analytical function

delete from emp
where rowid in 
   rowid rid,
   row_number() over (partition by empno,ename,job,mgr,hiredate,sal,comm,deptno order by rowid) rn
  from emp
 where rn <> 1 

Hope help and thank you for reading.

Sign Up to vote for this article
About Author
Nimish Garg
Occupation-Software Engineer
Company-Indiamart Intermesh Limited
Member Type-Junior
Joined date-04 Jun 2010
Home Page-
Blog Page-
Oracle DBA (Performance Tuning and Query Optimization) IndiaMART InterMESH Limited, Noida
Other popularSectionarticles
There is no comments for this articles.
Leave a Reply
Display Name:
(not display in page for the security purphase)
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top