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)