I am searching for a way to delete the duplicate records in a table..
Is anybody having efficient way to do it..?
 
Manoj Lonikar

The command is:
 
DELETE <duplicate_col_name> dv
FROM <table_name> a WHERE rowid <
  (SELECT MIN(rowid)
   FROM <table_name> b
   WHERE a.dv = b.dv);
 
i.e. if the table name is emp and the dupicate column value is ename:
 
DELETE ename
FROM emp a
WHERE rowid <
(SELECT MIN(rowid)
 FROM emp b
 WHERE a.ename = b.ename)
 
Another solution is:
 
delete from <table_name> where rowid not in (
select max(rowid) from <table_name> group by <duplicate_values_field_name>);
 
Rupjit

Quick Links:
Get help for your Oracle Database or SQL Related Problems
Do you have an Oracle Question?
 

Best regards,
Oracle Database, SQL, Application, Programming Tips