How to Delete Duplicate Records in Oracle Database?

To delete the duplicate records we need to first find all the distinct records. In duplicate records, every data is the same except row_id because row_id is the physical address that the record occupies. So we find the distinct row id where data are distinct in each column and then delete all the rows with row_id that are not in the above query.

Here we will see, how to delete duplicate records in Oracle. For this purpose of demonstration, we will be creating a Table Demo.

Create database:

Firstly, we create a table and fill data in it, with some records being duplicates using:

Query:

CREATE TABLE Demo( PersonID int, LastName varchar(255), FirstName varchar(255));
INSERT INTO Demo VALUES (1, 'Geek1', 'Geeksforgeeks');
INSERT INTO Demo VALUES (2, 'Geek2', 'Geeksforgeeks');
INSERT INTO Demo VALUES (3, 'Geek3', 'Geeksforgeeks');
INSERT INTO Demo VALUES (1, 'Geek1', 'Geeksforgeeks');
INSERT INTO Demo VALUES (2, 'Geek2', 'Geeksforgeeks');
INSERT INTO Demo VALUES (2, 'Geek2', 'Geeksforgeeks');

Output:

Now, we will find all the distinct records having no duplicates row using:

Query:

SELECT * FROM Demo 
WHERE rowid IN 
( SELECT MAX(rowid)  
FROM Demo
GROUP BY PersonID, LastName, FirstName);

Output:

Now. delete all the rows with rowid that are not in the above query using:

Query:

DELETE Demo  
WHERE rowid NOT IN 
  (SELECT MAX(rowid)  
FROM Demo
GROUP BY PersonID, LastName, FirstName);

Output:

The final table with duplicates removed is following: