Link to home
Start Free TrialLog in
Avatar of sam2929
sam2929

asked on

delete dups

Hi,
How can i delete the dups
key      om_key   aid              l1_cd   l2_cd  l3_d
2310      5662      28500          108     106
2311      5662      28500           108     106
2312      5662      28501          107     200
2313      5662      28501           107     200  800


i want to delete
2310      5662      28500          108     106

as there are many records so delete should be based on om_key
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

when are two records considered a dup and which one would you want to delete?

according to the data you posted, you might want something like

delete
from mytable t1
where exists (select 1 from mytable t2 where t1.key < t2.key and t1.om_key = t2.om_key and t1.aid = t2.aid and t1.l1_cd = t2.l1_cd and t1.l2_cd = t2.l2_cd)
Avatar of sam2929
sam2929

ASKER

Delete where key is lowest

2310      5662      28500          108     106
ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
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