posted 15 years ago
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