Link to home
Start Free TrialLog in
Avatar of Scott Palmer
Scott PalmerFlag for United States of America

asked on

Delete duplicates in Oracle table

Oracle database, using PL SQL
I have duplicates in a table and to find them I have to look at 4 fields.  This is the query I used to find the duplicates.

SELECT a.srv_date, a.proc_code, a.rend_prv_npi, a.cin_number, count(*) as qty
FROM Current_Data_Final a
GROUP BY a.srv_date, a.proc_code, a.rend_prv_npi, a.cin_number
HAVING count(*)> 1;

I need a query to delete the duplicates.

Thanks,
Scott
ASKER CERTIFIED SOLUTION
Avatar of Scott Palmer
Scott Palmer
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Bill Prew
Bill Prew
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Scott Palmer
Scott Palmer
Flag of United States of America image

ASKER

Sorry, here is the solution.  It seemed to be the cleanest way to do it and easy to read.  You can match on 1 field or as many as you need.

delete from  
   Current_Data_Final a
 where
   a.rowid > 
    any (select b.rowid
    from
       Current_Data_Final b
    where
       a.rend_prv_npi = b.rend_prv_npi
    and
      a.srv_date = b.srv_date
    and
      a.proc_code = b.proc_code
    and
      a.cin_number = b.cin_number)
 ;