Question: How to identify and delete the duplicate rows in the table?
Method - 1
SQL> DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);
Method - 2
SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table_name1;
SQL> rename table_name2 to table_name1;
SQL> -- Remember to recreate all indexes, constraints, triggers, etc on table ...
Method - 3
SQL> delete from my_table t1
where exists
( select 'x' from my_table t2
where t2.key_value1 = t1.key_value1
and t2.key_value2 = t1.key_value2
and t2.rowid > t1.rowid
);
Method - 4
SQL> delete from my_table where rowid in
( select rowid from
(select rowid, row_number() over (partition by key_value1, key_value2 order by key_value1, key_value2) seq from my_table t)
where seq <> 1
);
Method - 5
SQL> delete from my_table
where rowid not in
( select min(rowid) from my_table group by key_value1, key_value2);
Reference: Finding and Eliminating Duplicate Data from devx.com also gives a good and details regarding to the topic.
...
