How to 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.
SQL> DELETE FROM SAMPLE A WHERE ROWID > (SELECT MIN(ROWID) FROM SAMPLE B WHERE A.ID=B.ID);
1 row deleted.
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;
SQL> DROP TABLE SAMPLE;
SQL> RENAME SAMPLE2 TO SAMPLE;
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.
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.
Get Free Blogging Tips & Technology updates in your Email !!!
Latest posts by Anil Verma (see all)
- BlueHost Affiliate Program – Earn $65 Per Referral - August 2, 2015
- WP Engine Affiliate Program – Earn $200 Per Referral - August 1, 2015
- How to fill out BlueHost Affiliate Tax form for Non-US Residents - June 20, 2015