Oracle Remove Duplicate from Table

Let's explore a one effective method to remove duplicate rows from an Oracle table.

Duplicate data can be a common problem when working with databases, and it's crucial to eliminate such redundancy to ensure data accuracy and improve query performance.

Using this simple query we can find and DELETE duplicate rows from Oracle TABLE.

DELETE FROM your_table
WHERE rowid NOT IN 
(
    SELECT MIN(rowid)
    FROM your_table
    GROUP BY column1
    	   , column2
           , column3
);

This solution uses the GROUP BY clause, which clusters the records based on the uniqueness of specified columns. Selecting the MIN(rowid) for each group, we ensure that one original record remains, while the rest are deleted.

Example Using a Dummy Table

Here is our sample table.

To remove the duplicates from this table we simply execute the following SQL.

DELETE FROM EMPLOYEE
WHERE ROWID NOT IN 
(
    SELECT MIN(ROWID)
    FROM EMPLOYEE
    GROUP BY FIRST_NAME
           , LAST_NAME
           , DEPT_ID
)

Stay tuned for more insights and tips to keep your Oracle databases lean and performant!