• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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: 41915
910
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.
 
Why am I so drawn to cherry pie? I can't seem to stop. Save me tiny ad!
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic