Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    56

    Question Unanswered: Delete statement

    DELETE FROM S.I73_FNDSRC_PTYPE_T
    WHERE I73_NEED_ID =
    (SELECT I74_NEED_ID
    FROM S.I74_NEED_SERVICE_T
    WHERE I74_CAP_ID = CAP_ID);

    The above code is part of a stored procedure that I'm writing. The delete statement works if the tables I73 & I74 each have only one row where I73_NEED_ID = I74_NEED_ID. However, if there is more than one row returned, I get a scalar function error and the stored procedure terminates.

    Is it possible to rewrite the above code using some type of join?
    Where could I find an example?

    Thanks in advance.

  2. #2
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by citi
    DELETE FROM S.I73_FNDSRC_PTYPE_T
    WHERE I73_NEED_ID =
    (SELECT I74_NEED_ID
    FROM S.I74_NEED_SERVICE_T
    WHERE I74_CAP_ID = CAP_ID);

    The above code is part of a stored procedure that I'm writing. The delete statement works if the tables I73 & I74 each have only one row where I73_NEED_ID = I74_NEED_ID. However, if there is more than one row returned, I get a scalar function error and the stored procedure terminates.

    Is it possible to rewrite the above code using some type of join?
    Where could I find an example?

    Thanks in advance.
    the right high side
    (SELECT I74_NEED_ID
    FROM S.I74_NEED_SERVICE_T
    WHERE I74_CAP_ID = CAP_ID)
    is called scalar fullselect, which must result only one row(otherwise, sqlcode)

    you can use '= any' predicate, such as
    DELETE FROM S.I73_FNDSRC_PTYPE_T
    WHERE I73_NEED_ID = ANY
    (SELECT I74_NEED_ID
    FROM S.I74_NEED_SERVICE_T
    WHERE I74_CAP_ID = CAP_ID);

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by nidm
    the right high side
    (SELECT I74_NEED_ID
    FROM S.I74_NEED_SERVICE_T
    WHERE I74_CAP_ID = CAP_ID)
    is called scalar fullselect, which must result only one row(otherwise, sqlcode)

    you can use '= any' predicate, such as
    DELETE FROM S.I73_FNDSRC_PTYPE_T
    WHERE I73_NEED_ID = ANY
    (SELECT I74_NEED_ID
    FROM S.I74_NEED_SERVICE_T
    WHERE I74_CAP_ID = CAP_ID);
    = ANY is the same as an IN-operator.

    Code:
    WHERE I73_NEED_ID IN ( SELECT ... )
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Aug 2007
    Posts
    56

    Smile

    Thank you both for your replies!

Posting Permissions

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