Query to delete one of the duplicate rows

[muzu1232004]

Is there any query which will delete exactly one of the duplicate rows in a table and retain only one ?

[Saii]

Yes there are different ways to delete duplicate rows. What problem are you facing with your query?

[amitpatel66]

DELETE FROM <TABLE_NAME> WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM <TABLE_NAME> GROUP BY <COLUMN_NAME> HAVING COUNT(COLUMN_NA ME) >= 1)

Say you have following data:

Table emp_det

EMPID ENAME
101 A
101 A
102 B
102 B

Then the below query will delete the duplicate records from emp_det table.

DELETE FROM emp_det WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM emp_det GROUP BY empid HAVING COUNT(empid) >= 1)

[muzu1232004]

Thanks for ur reply.
i had the same problem.

[hashim]

hi
1-first u have to select the rowid for this rows
using flowing code:
select rowid from <table_name> where <condition >
2- delete from <table_name> where rowid=<choose the rowid from previous query>
3-commit