Different Methodologies To Remove/Delete Duplicate Records[Rows] From A Table In Oracle Database

How To Remove/Delete Duplicate Rows/Records From A Table

 

Redundant data decreases the performance of a query and waste the space in the database. Hence its always better to remove the duplicate records or rows from a table. The removal process from oracle database tables with SQL statements can be really tricky some times. There are several methods which can be used to remove the redundant data[duplicate rows] from a table. Some of these standard methods are described below:

Using subquery and ROWID to delete duplicate rows:

DELETE FROM emp A
WHERE a.rowid > ANY (
                      SELECT B.rowid FROM emp B
                      WHERE A.Emp_Id = B.Emp_Id
                      AND A.Name = B.Name
                      AND A.Phone = B.Phone
        );

OR

DELETE FROM emp A 
  WHERE ROWID > ( SELECT min(rowid) 
                 FROM emp B
                 WHERE A.Emp_Id = B.Emp_Id );

Using RENAME or Another Temporary Table to delete duplicate rows::

create table emp2 as select distinct * from emp;
drop table emp;
rename emp2  to emp; 

Using EXIST to delete duplicate rows:

DELETE FROM emp t1
WHERE EXISTS ( SELECT 'x' FROM emp t2
               WHERE t2.Emp_Id = t1.Emp_Id
                 AND t2.Name = t1.Name 
                  AND t2.rowid      > t1.rowid );  

Using NOT IN to delete duplicate rows:

DELETE FROM emp t1

WHERE rowid NOT IN ( SELECT min(rowid) 
                     FROM emp t2
                     GROUP BY Emp_Id, Name ); 

Using analytic Function to delete duplicate rows: 

DELETE FROM emp
WHERE ROWID IN
  (SELECT ROWID FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY Emp_Id ORDER BY Emp_Id) rnk FROM emp)
    WHERE rnk>1); 

Also Read:

1. How To Eliminate The Duplicate Rows From A Table