Post reply

correlated subquery in delete statement?

  • June 21, 2004 at 9:41 am

    #161733

    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

    #511279

    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

    #511541

    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

    #511614

    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