Different ways of deleting duplicate rows in Oracle SQL . dEexams.com

Consider the emp table with below rows


CREATE TABLE  
emp(
    empno INTEGER,
    empname VARCHAR2(20),
    salary NUMBER
    );

10   Bill    2000
11   Bill    2000
12   Mark    3000
12   Mark    3000
12   Mark    3000
13   Tom     4000
14   Tom     5000
14   Susan   5000

Using ROWID

DELETE FROM emp WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp GROUP BY empno );

This technique can be applied to almost scenarios. GROUP BY operation should be on the columns which identify the duplicates.
Using self-join

DELETE FROM emp e1 WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp e2 WHERE e1.empno=e2.empno );
Using ROW_NUMBER()

DELETE FROM emp WHERE ROWID IN (SELECT rid FROM ( SELECT ROWID rid, ROW_NUMBER() OVER(PARTITION BY empno ORDER BY empno) rn FROM emp ) WHERE rn > 1 );

This is another efficient way to delete duplicates.
Using DENSE_RANK()

DELETE FROM emp WHERE ROWID IN (SELECT rid FROM ( SELECT ROWID rid, DENSE_RANK() OVER(PARTITION BY empno ORDER BY ROWID ) rn FROM emp ) WHERE rn > 1 );

Here you can use both RANK() and DENSE_RANK() , since both will give unique records when ORDER BY ROWID.
Using GROUP BY

DELETE FROM emp WHERE (empno,empname,salary) IN ( SELECT MAX(empno),empname,salary FROM emp GROUP BY empname,salary);

Uk01 on Feb 01, 2015 at 12:02 am

If you like dEexams.com and would like to contribute, you can write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.