Delete Duplicate Records in Oracle

Delete Duplicate Records in Oracle

There are times when duplicate rows somehow creep into a table. The best scenario to happen this is when the data is to be imported from some other table or data and the Constraints are removed so that data import successfully. Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques for identifying and removing duplicate rows from tables:

CREATE TABLE dup_test (
Emp_Id VARCHAR2(5),
Name VARCHAR2(15),
Phone NUMBER);

INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('100','John',473256);
INSERT INTO dup_test values('101','Dave',561982);

SELECT * FROM dup_test;
Use subquery to delete duplicate rows:

Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:

DELETE FROM
dup_test A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
dup_test B
WHERE
A.Emp_Id = B.Emp_Id
AND
A.Name = B.Name
AND
A.Phone = B.Phone
);
Use analytics to delete duplicate rows:

You can also detect and delete duplicate rows using Oracle analytic functions:

DELETE FROM dup_test
WHERE ROWID IN
(SELECT ROWID FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Emp_Id ORDER BY Emp_Id) rnk FROM dup_test)
WHERE rnk>1);
Use another table to delete duplicate rows:

This is the simplest method to remove duplicity.

CREATE TABLE dup_test_1 as select distinct * from dup_test;
DROP TABLE dup_test;
RENAME dup_test_1 to dup_test;
Use RANK to delete duplicate rows:

This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row:

DELETE FROM dup_test where rowid in
(
select "rowid" from
(select "rowid", rank_n from
(select rank() over (partition by Emp_Id order by rowid) rank_n, rowid as "rowid"
from dup_test
)
)
where rank_n > 1
);
The above methods are only standard methods. You can also use your own techniques to remove duplicate records.