To make a long story short (too late!), I have an indexed archive table which is loaded via SQL*LOADER which I recently noticed has a disabled index because at some point in the past the same data file was inserted twice, creating about 7000 duplicate records. How would one write a PL/SQL script which would delete duplicate rows in a table?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How does one delete duplicate rows from a table? 2
- Thread starter weberm
- Start date
-
2
- #2
To make a long story short I'd suggest you to search this forum as such question is asked at least once per month 
Regards, Dima
Regards, Dima
- Thread starter
- #3
![[purpleface]](/data/assets/smilies/purpleface.gif "[purpleface] [purpleface]")
Hmmmh I have a stinker of a delete to look at and have not a clue, bit more complex than the average I feel.
Table A sys_id, sys_type, security_id
Table B sys_id, sys_ver_id, release
one entry in A can have multiple in B but the sys_id should be maintained throughout and new sys_ver_id values added as the releases increase.
Unfortunately a loader program has decided that rather than
grab the existing sys_id from A when updating or expanding a new release on a particular security_id, it will instead create a whole new rec in A and a corresponding one in B.
Leads to having anything from 2 -> 20 repeated A & B recs against a particular security ID.
Ideally I'd like to delete all but one rec for A against the individual security_id and then tie the B recs all to that sys_id.
Hope I've explained this someway sensible any ideas where to start with this ??
Cheers,
Mully
Table A sys_id, sys_type, security_id
Table B sys_id, sys_ver_id, release
one entry in A can have multiple in B but the sys_id should be maintained throughout and new sys_ver_id values added as the releases increase.
Unfortunately a loader program has decided that rather than
grab the existing sys_id from A when updating or expanding a new release on a particular security_id, it will instead create a whole new rec in A and a corresponding one in B.
Leads to having anything from 2 -> 20 repeated A & B recs against a particular security ID.
Ideally I'd like to delete all but one rec for A against the individual security_id and then tie the B recs all to that sys_id.
Hope I've explained this someway sensible any ideas where to start with this ??
Cheers,
Mully
- Status
Similar threads
