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:
https://oraclebrains.com/?p=115
0 responses so far ↓
There are no comments yet...Kick things off by filling out the form below.
Leave a Comment