Scenario From Oracle Forum

I have a transaction table consists of duplicate records I success to write a sql
which get the duplicate records but unable to write the delete statement to delete
this record.

The sql query is as follows:

Select invoice_no, slip_no, drug_code
from transaction_detail
having count(*)>1
group by invoice_no, slip_no, drug_code

If anyone knows please let me know the delete statement. Thanks in advance.

Regards

This is what I think

DELETE transaction_detail td1
WHERE EXISTS ( select ‘x’ FROM
(Select MIN(invoice_no) invoice_no, MIN(slip_no) slip_no, MIN(drug_code) drug_code, MIN(rowid) t_rowid
from transaction_detail
having count(*)>1
group by invoice_no, slip_no, drug_code) td2
WHERE td1.invoice_no = td2.invoice_no
AND td1.slip_no = td2.slip_no
AND td1.drug_code = td2.drug_code
AND td1.rowid td2.t_rowid);

Check out following url for more interesting sql regarding deleting of duplicates:

http://www.oraclebrains.com/?p=115