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.
