OracleBrains.Com header image 2

Deleting duplicate row in certain condition using Join!

December 1st, 2007 by Rajender Singh · No Comments

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:

https://oraclebrains.com/?p=115


Tags: Interesting Coding Showcase · SQL and PL/SQL

0 responses so far ↓

  • There are no comments yet...Kick things off by filling out the form below.

Leave a Comment