Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unhappy Unanswered: SQL Delete HELP!

    I've got this select:

    SELECT * FROM T.TD0072 A, T.TD0074 B
    WHERE A.SHIP_TO_NAME='???'
    AND A.ORDER_NBR = B.ORDER_NBR
    AND A.SEQUENCE_NBR = B.SEQUENCE_NBR
    AND B.CURR_STATUS <> 'P'

    I need to convert it into a delete. Can someone help me with the syntax...

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: SQL Delete HELP!

    AFAIK, you will need two delete statements for this one ...

    I assume this is should work:

    delete from T.TD0072 where ship_to_name='???' and (order_nbr,sequence_nbr) in (select order_nbr,sequence_nbr from T.TD0074 where CURR_STATUS <> 'P' )

    delete from T.TD0074 where CURR_STATUS <> 'P' and (order_nbr,sequence_nbr) not in (select order_nbr,sequence_nbr from
    T.TD0072 where ship_to_name='???')

    I have not tested this ...

    Cheers
    Sathyaram

    Originally posted by j8h9
    I've got this select:

    SELECT * FROM T.TD0072 A, T.TD0074 B
    WHERE A.SHIP_TO_NAME='???'
    AND A.ORDER_NBR = B.ORDER_NBR
    AND A.SEQUENCE_NBR = B.SEQUENCE_NBR
    AND B.CURR_STATUS <> 'P'

    I need to convert it into a delete. Can someone help me with the syntax...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Deleting from td0074 as above does not work ..

    Let me make as assumtion ... A row can exist in td0074 only if there is a corresponding order_num,sequence_num in td0072

    in that case ...
    delete from T.TD0074 where (order_nbr,sequence_nbr) not in (select order_nbr,sequence_nbr from
    T.TD0072 )

    If the assumtion is not correct, let me know

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •