• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

duplicate rows

 
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,

I've a table TXN_SC have many columns in it and contains about 160,000 records. Constarint set on this table is one columns Id,gpcode,loc,le_id,ext_id.

One more column called sys_id which is not a part of key along withn this there are other several columns.

I have many duplicates records for the combiantion of Id,gpcode,loc,ext_id,sys_id,ext_id,le_id and all these duplicate rows need to be retained .

But duplicate records fro the combiantion of Id,gpcode,loc,ext_id,sys_id,ext_id and whose le_id are different such rows need to be deleted.

I tried some thing like this

delete from TXN_SC sc where (sc.loc,sc.ext_id,sc.sys_id,sc.le_id) in (select a.loc,a.ext_id,a.sys_id,a.le_id where Id=? and gpcode=? group by a.loc,a.ext_id,a.sys_id,a.le_id having count(*)=1)

This deletes even those records whose occuarance is only one in entrire set.Please sugest how to go about it.

Thanks











 
author & internet detective
Posts: 42102
933
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Prashanth,
I would use the Oracle specific row num keyword to find records that have duplicate data and more than one row.
 
After some pecan pie, you might want to cleanse your palatte with this tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic