5 Ways to Delete Duplicate Rows from Oracle Table with SQL

SiteGround Hosting Discount

There are several techniques for identifying and removing duplicate records from Oracle Tables with SQL. The data can be duplicated in the absence of Primary/Unique key or inserting data in batch processing. This article explain you different ways to delete duplicate rows from Oracle Table.

Delete Duplicate Rows from Oracle Table with SQL

Let's create a table to understand the examples in a better way.

CREATE TABLE test_data
( SR_NO NUMBER,
NAME VARCHAR2(30),
DEPT_ID NUMBER,
CITY VARCHAR2(30));


INSERT INTO test_data VALUES(1, 'Ankit', 1001, 'Gurgaon');
INSERT INTO test_data VALUES(2, 'Sudhir', 1002, 'New Delhi');
INSERT INTO test_data VALUES(1, 'Ankit', 1001, 'Gurgaon');
INSERT INTO test_data VALUES(1, 'Ankit', 1001, 'Gurgaon');
INSERT INTO test_data VALUES(3, 'Anamika', 1003, 'Jaipur');
INSERT INTO test_data VALUES(2, 'Sudhir', 1002, 'New Delhi');

COMMIT;

Method-1: Using MIN(rowid) & MAX(rowid)

This is the most common and widely used method of removing duplicate rows.

DELETE FROM test_data
WHERE ROWID NOT IN (SELECT  MIN (ROWID) FROM test_data
GROUP BY SR_NO, NAME, DEPT_ID,CITY);


OR

DELETE FROM test_data
WHERE ROWID NOT IN (SELECT  MAX (ROWID) FROM test_data
GROUP BY SR_NO, NAME, DEPT_ID,CITY);

Method-2: Using MIN(rowid) & Self Join

This is little bit different from above method.

DELETE FROM test_data td1
WHERE td1.ROWID NOT IN (SELECT MIN (td2.ROWID) FROM test_data td2
WHERE td2.SR_NO = td1.SR_NO AND td2.NAME = td1.NAME
AND td2.DEPT_ID = td1.DEPT_ID AND td2.CITY = td1.CITY);

OR

DELETE FROM test_data td1 WHERE ROWID > (
SELECT MIN(rowid) FROM test_data td2
WHERE td2.SR_NO = td1.SR_NO AND td2.NAME = td1.NAME
AND td2.DEPT_ID = td1.DEPT_ID AND td2.CITY = td1.CITY);

Method-3: Drop & Rename Table

Use this method very cautiously as it will first drop your table and then recreates it.

CREATE TABLE test_data2 as SELECT distinct * FROM test_data;
DROP TABLE test_data;
RENAME test_data2 TO test_data;

Method-4: Using Exists

This method is also widely used to delete duplicate rows from Oracle Table.

DELETE FROM test_data t1
WHERE EXISTS (SELECT 'X' FROM test_data t2
WHERE t2.SR_NO=t1.SR_NO AND t2.ROWID>t1.ROWID);

Method-5: Using Analytics

You can also delete duplicate rows using Oracle analytic function.

DELETE FROM test_data
WHERE rowid in
(SELECT rowid FROM
(SELECT rowid, row_number() over
(partition by SR_NO, NAME, DEPT_ID, CITY order by SR_NO) dupl
FROM test_data) WHERE dupl > 1);

You can also write your method in comment section. We will incorporate the same in this article with Recommended by [Your Name].

I use SEMRush for Keyword Research & Boost Organic Traffic. Do You?

Highly recommended SEM & SEO Tool for overview of Traffic, Keywords, Backlinks and more. You can search your competitors URL to analyze keywords & crush your competition.



Leave a Reply

Your email address will not be published. Required fields are marked *