Oracle PL/SQL Tips: Deleting Duplicate Rows

You can use one of the following queries to identify and/or remove duplicate rows from a table leaving only unique records in the table.

Method 1: Based on key field(s), delete all rows which have greater ROWIDs than the smallest ROWID. ROWID is a pseudocolumn that uniquely identifies a row within a table.

DELETE FROM table_name A
WHERE ROWID > ( SELECT min(ROWID)
              FROM table_name B
              WHERE A.key_field = B.key_field );

--delete all rows which have smaller ROWIDs than
--the biggest ROWID for given key values
DELETE FROM table_name A
WHERE ROWID > ( SELECT max(ROWID)
                FROM table_name B
                WHERE A.key_field = B.key_field );

Method 2: Almost similar to Method 1, but using group by key field(s) criteria to delete.

DELETE FROM my_table t1
WHERE ROWID NOT IN ( SELECT min(ROWID)
                 FROM my_table t2
                 GROUP BY key_field1, key_field2 );