How Delete duplicate rows from table in oracle

No.of Views3030
Bookmarked0 times
Downloads 
Votes0
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 info@codegain.com

 

Introduction

http://nimishgarg.blogspot.com/2011/01/oracle-delete-duplicate-rows-from-table.html

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 
( 
 select 
  rid
 from 
 ( 
  select 
   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
Location-India
Joined date-04 Jun 2010
Home Page-http://nimishgarg.blogspot.com/
Blog Page-http://nimishgarg.blogspot.com/
Oracle DBA (Performance Tuning and Query Optimization) IndiaMART InterMESH Limited, Noida
 
 
Other popularSectionarticles
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
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
</