On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Tuesday, October 21, 2008

Delete Duplicate Records


This logic was found on PL/SQL Pipeline:
To test, insert a duplicate 'SMITH' on the EMP TABLE:
Insert Into Emp Values(9999,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
Commit;
You can see what is happening in the above report, and how a record is being chosen for deletion by running the following query. Records are being ranked (and a RowId saved) by breaking all the employees into partitions based on their name, hire date, and department. Within each 'partition,' they are ordered by the Rowid.
Select Rowid, Empno, EName, HireDate, Deptno,
Rank() Over (Partition By EName, HireDate, DeptNo
Order By Rowid)
As
Ranking
From Emp;

This sets us up the delete:
Delete From Emp 
Where Rowid In
( Select Rowid
From
( Select Rowid,
Rank() Over (Partition By EName, HireDate, DeptNo
Order By Rowid)
As
Ranking
From Emp
)
Where Ranking > 1
);

1 comment:

  1. The task of deleting the duplicate entries can be achieved by this following simple method......
    DELETE FROM EMP A
    WHERE ROWID!=(SELECT MAX(ROWID) FROM EMP B WHERE A.ENAME=B.ENAME);

    ReplyDelete