June 21, 2004 at 9:41 am
Hi,
I have created an identity column on my table called ROWNUM. I want to delete duplicates from the table using the rownum. Can I use a correlated subquery (using an alias) on the delete statement?
i.e. delete from NA_ATTRIBUTE_DATA t where t.ROWNUM <> (select MAX(x.ROWNUM) from NA_ATTRIBUTE_DATA x where x.MONBR = t.MONBR)
June 21, 2004 at 9:58 am
Solved it myself. Thanks anyway.
delete from NA_ATTRIBUTE_DATA where NA_ATTRIBUTE_DATA.ROWNUM (select MAX(x.ROWNUM) from NA_ATTRIBUTE_DATA x where x.MONBR = NA_ATTRIBUTE_DATA.MONBR)
June 22, 2004 at 7:28 pm
Here is an elegant way to do this:
delete t1
from NA_ATTRIBUTE_DATA t1
JOIN NA_ATTRIBUTE_DATA t2 on t1.MONBR = t2.MONBR
where t1.ROWNUM < t2.ROWNUM
This will delete all records in the table where the MONBR is the same and the ROWNUM is not max.
Signature is NULL
June 23, 2004 at 7:42 am
Thanks Calvin,
Much rather have the elegant approach. I'll try it out.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply