Frequently Asked Questions (FAQs)

QUESTION : How do I delete duplicate entries from a table ?

---

ANSWER :

Option 1) The simplest and most elegant Method
CREATE TABLE table_without_duplicates AS
SELECT DISTINCT *
FROM   table_with_duplicates ;
COMMIT;

DROP TABLE table_with_duplicates ;
COMMIT;
RENAME TABLE table_without_duplicates table_with_duplicates;

Option 2) An Oracle-specific Method
For Oracle, this solution utilizes the unique rowid of each row in a table,
and removes all duplicate rows in a table:
 
DELETE  FROM my_table t
WHERE EXISTS (
        SELECT *
        FROM my_table tt
        WHERE t.rowid      > tt.rowid 
        AND   tt. = t. 
        AND ... tt.value-n = t.value-n)