How to Delete Duplicate Rows from a Table

Delete Duplicate Rows from a Table

How to remove duplicate records from a Table?

If you want to remove duplicate rows from a Table and want to leave only unique records in a Table then choose any one of the following method to delete duplicate rows.

Method-1:

SQL> DELETE FROM SAMPLE A WHERE ROWID > (SELECT MIN(ROWID) FROM SAMPLE B WHERE A.ID=B.ID);


1 row deleted.

SQL> COMMIT;


Commit complete.

Method-2:

Note: This method is not preferable because here you have to drop the table from database and that is not possible on any production database.

SQL> CREATE TABLE SAMPLE2 AS SELECT DISTINCT * FROM SAMPLE;

Table created.

SQL> DROP TABLE SAMPLE;

Table dropped.

SQL> RENAME SAMPLE2 TO SAMPLE;

Table renamed.

Method-3:

SQL> DELETE FROM SAMPLE S1

WHERE EXISTS (SELECT ‘X’ FROM SAMPLE S2

WHERE S2.ID=S1.ID AND S2.ROWID>S1.ROWID);

You can choose Method-1 or Method-3 to delete duplicate rows from a table.

What is the Difference between Truncate and Delete in Oracle

If you know any other method to eliminate duplicate rows from a Table, please write them on comment section. We will definitely incorporate them in our article.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)