How do I eliminate the duplicate rows ?
-
delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);
-
delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);
-
delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);
-
Delete all
All three approaches (A, B, C) are valid SQL techniques for deleting duplicate rows while keeping one instance. They use ROWID (Oracle) or equivalent row identifiers to identify and delete duplicates. Option A keeps the max ROWID; B and C keep the min ROWID per duplicate group. Option D would delete everything.
These three DELETE statements are the classic correlated-subquery patterns for removing duplicate rows in Oracle/SQL, each keeping one representative row (the one with the minimum or maximum ROWID) per duplicate group and deleting the rest: (1) deletes every row whose ROWID isn't the max ROWID for its group, (2) and (3) are the same idea phrased with a 'where rowid < (select min(rowid)...)' correlated subquery, keeping the first-inserted row. All three are legitimate, commonly-taught techniques for the same task, just written slightly differently (including the well-known 'ename/emp' textbook example). 'Delete all' is wrong because it removes every row, not just duplicates, defeating the purpose of deduplication.