Oracle: Delete duplicate rows from table

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
)
Get More Query Optimization Tips:
http://nimishgarg.blogspot.in/2012/10/query-optimization-tips-for-oracle.html




Related Posts:
- Oracle - DDL, DML and DCL commands
- ORA-01439: column to be modified must be empty to change datatype
- ORA-02438: Column check constraint cannot reference other columns
- Oracle 11g: Virtual Column


6 comments:

  1. This[1. Simple & Easy Way] helped me to solve my prob.But how to do it with multiple table where same rowid is foreign key in other table.?

    Thanks NIMISH

    ReplyDelete
  2. if u need to delete duplicate rows from master/child tables u may use delete with cascade option

    ReplyDelete
  3. i used the 2nd way for my problem

    ReplyDelete
  4. 2nd way is one of the my preferred way...

    ReplyDelete
  5. Hi Nimish,

    I have duplicate rows in which some of them are in upper case & some are in lower case. I want to delete lower case data. I want to use 2nd way for my problem
    Any idea....

    ReplyDelete
  6. u may simply convert ur all data to upper case and then remove duplicate records.

    ReplyDelete