Oracle Remove Duplicate Rows

By ruzaib khan

Duplicate error are remove in a oracle table.

delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
or
delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid)  from table_name tb where ta.dv=tb.dv);

Example.
Table Emp
Empno Ename
101               Scott
102               Jiyo
103               Millor
104               Jiyo
105               Smith

delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);

The output like,
Empno Ename
101               Scott
102               Millor
103               Jiyo
104               Smith

Oracle Remove Duplicate Rows  (885 Views)