Tuesday, May 20, 2014

How to Delete Duplicate Rows in a Oracle Table

---->> ========================================================================
SELECT * FROM DEPT;

INSERT INTO DEPT SELECT * FROM DEPT WHERE DEPTNO = 20;
INSERT INTO DEPT SELECT * FROM DEPT WHERE DEPTNO = 10;
INSERT INTO DEPT SELECT * FROM DEPT WHERE DEPTNO = 30;
INSERT INTO DEPT SELECT * FROM DEPT WHERE DEPTNO = 20;

commit;

SELECT * FROM DEPT;

select * from dept group by DEPTNO, DNAME, LOC having count(*) > 1;
---->> ========================================================================

==> delete from dept where rowid in (select rowid from dept minus select max(rowid) from dept group by DEPTNO, DNAME, LOC);

==> delete from dept where rowid NOT in (select min(rowid) from dept group by DEPTNO, DNAME, LOC);

==> DELETE FROM DEPT A WHERE a.rowid > (SELECT min(B.rowid) FROM DEPT B WHERE A.DEPTNO = b.DEPTNO AND A.DNAME = B.DNAME and A.LOC=B.LOC);

==> delete from dept t1 where rowid != (select max(rowid) from dept b where b.deptno = t1.deptno and b.dname = t1.dname and b.loc=t1.loc);

==> delete from dept t1 where exists (select 'x' from dept t2 where t2.deptno = t1.deptno and t2.dname = t1.dname and t1.loc = t2.loc and t2.rowid  > t1.rowid);

==> DELETE FROM dept a WHERE (a.deptno,a.dname,a.loc) IN (SELECT b.deptno,b.dname,b.loc FROM dept b WHERE a.deptno = b.deptno AND a.dname = b.dname AND a.loc = b.loc AND a.ROWID  > b.ROWID);

==> DELETE FROM DEPT A WHERE a.rowid > ANY (SELECT B.rowid FROM DEPT B WHERE A.DEPTNO = b.DEPTNO AND A.DNAME = B.DNAME and A.LOC=B.LOC);

==> delete from dept where rowid in (select rowid from (select rowid, row_number() over (partition by deptno order by deptno) dup from dept) where dup > 1);

==> delete from dept where rowid IN (select rowid from (select rowid, row_number() over (partition by deptno,dname,loc order by rowid) rn from dept) where rn <> 1);

==> delete dept where rowid in(select lead(rowid) over (partition by deptno order by null) from dept);

==> DELETE FROM dept WHERE rowid IN
    (SELECT "rowid"
     FROM
     (SELECT "rowid", rank_n
      FROM
      (SELECT rank() over (partition BY deptno order by rowid) rank_n, rowid AS "rowid"
       FROM dept
       WHERE deptno IN (SELECT deptno FROM dept GROUP BY deptno,dname,loc HAVING COUNT(*) > 1)
      )
     )
     WHERE rank_n > 1
    );

==> DELETE FROM dept WHERE deptno in (SELECT deptno FROM dept WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM dept GROUP BY deptno));

==> delete  from dept where ('Delete',deptno) in (select case when count(*)>1 then 'Delete' else 'No' end DeleteFlag ,deptno from dept group by deptno having count(*)>1);
---->> ========================================================================

1 comment:

Oracle Applications said...

Hey Raju, Thanks for sharing the process. While searching for Oracle Applications found your posts!!

Appreciate your efforts..

Post a Comment

Best Blogger TipsGet Flower Effect