How to delete duplicate rows from an Oracle Database?

Question

We have a table that has had the same data inserted into it twice by accident meaning most (but not all) rows appears twice in the table. Simply put, I'd like an SQL statement to delete one version of a row while keeping the other; I don't mind which version is deleted as they're identical.

Table structure is something like:

FID, unique_ID, COL3, COL4....

Unique_ID is the primary key, meaning each one appears only once. FID is a key that is unique to each feature, so if it appears more than once then the duplicates should be deleted.

To select features that have duplicates would be:

select count(*) from TABLE GROUP by FID

Unfortunately I can't figure out how to go from that to a SQL delete statement that will delete extraneous rows leaving only one of each.

This sort of question has been asked before, and I've tried the create table with distinct, but how do I get all columns without naming them? This only gets the single column FID and itemising all the columns to keep gives an: ORA-00936: missing expression

CREATE TABLE secondtable NOLOGGING as select distinct FID from TABLE

Answer 1

If you don't care which row is retained

DELETE FROM your_table_name a
 WHERE EXISTS( SELECT 1
                 FROM your_table_name b
                WHERE a.fid = b.fid
                  AND a.unique_id < b.unique_id )

Once that's done, you'll want to add a constraint to the table that ensures that FID is unique.

Answer 2

Try this

DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.FID = B.FID)