Removing duplicate rows from table in Oracle
Use the rowid pseudocolumn.
DELETE FROM your_tableWHERE rowid not in(SELECT MIN(rowid)FROM your_tableGROUP BY column1, column2, column3);Where column1, column2, and column3 make up the identifying key for each record. You might list all your columns.
From Ask Tom
delete from t where rowid IN ( select rid from (select rowid rid, row_number() over (partition by companyid, agentid, class , status, terminationdate order by rowid) rn from t) where rn <> 1);(fixed the missing parenthesis)
From DevX.com:
DELETE FROM our_tableWHERE rowid not in(SELECT MIN(rowid)FROM our_tableGROUP BY column1, column2, column3...) ;Where column1, column2, etc. is the key you want to use.