How to delete duplicate rows from a table in Oracle SQL

Lakshmi Ramasamy 2nd May 2020 Leave a Comment

In this article, we will see how to construct the single delete query to find and remove the duplicate rows from the table in Oracle SQL.

Identifing and deleting the duplicate rows

In Oracle there are many ways to delete duplicate records. Below are the some of those.

Consider the EMPLOYEES table with below rows.

CREATE TABLE EMPLOYEES (
        EMP_ID   NUMBER(10),
        EMP_NAME VARCHAR2(100),
        DESIGNATION VARCHAR2(50),
        PRIMARY KEY (EMP_ID)
);

EMP_ID      EMP_NAME      DESIGNATION
1			John		  HR
2			Taqueer		  Developer
3			Akilesh		  Project Manager
4			John		  HR
5			Taqueer		  Developer
6			Helen		  Internet Marketing Head
7			Taqueer		  Developer
8			Sai Shree	  System Administrator

Note:

1. group by should be on columns which identify the duplicates.
2. Use max()function to delete the old records.
3. Use min()function to delete new records.

1. Using `rowId`

select * from EMPLOYEES where rowid not in 
  (select max(rowid) from EMPLOYEES group by EMP_NAME);
  
delete from EMPLOYEES where rowid not in 
  (select max(rowid) from EMPLOYEES group by EMP_NAME);

2. Using self-join

select * from EMPLOYEES e1 where rowid not in
(select max(rowid) from EMPLOYEES e2 where e1.EMP_NAME = e2.EMP_NAME);

delete from EMPLOYEES e1 where rowid not in
(select max(rowid) from EMPLOYEES e2 where e1.EMP_NAME = e2.EMP_NAME);

3. Using `row_number()`

select * from EMPLOYEES where rowid in (select rid from ( select rowid rid,
row_number() over(partition by EMP_NAME order by EMP_NAME) rn from EMPLOYEES) where rn > 1);


delete from EMPLOYEES where rowid in (select rid from ( select rowid rid,
row_number() over(partition by EMP_NAME order by EMP_NAME) rn from EMPLOYEES) where rn > 1);

The above query will delete the newly inserted records.

4. Using `group by`

SELECT * FROM EMPLOYEES WHERE EMP_ID NOT IN
(SELECT MAX(EMP_ID) FROM EMPLOYEES GROUP BY EMP_NAME);

DELETE FROM EMPLOYEES WHERE EMP_ID NOT IN
(SELECT MAX(EMP_ID) FROM EMPLOYEES GROUP BY EMP_NAME);

Result:

EMP_ID  EMP_NAME    DESIGNATION
3		Akilesh		Project Manager
4		John		HR
6		Helen		Internet Marketing Head
7		Taqueer		Developer
8		Sai Shree	System Administrator

Always check once before committing the changes.

Points to take care of

1. First, identify the duplicates using select.
2. Double verify those are actual ‘duplicates’ or not and which one wants to delete.
3. Take backup if necessary
4. Do commit only if you are sure.