Online Tutorials & Training Materials | STechies.com
Register Login

Deleting duplicate rows in the table

|| || 0

Deleting duplicate rows in the table
Stechies

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



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>);



Related Articles