Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Question Unanswered: newby - compound UPDATE stmt help

    I want to execute the following UPDATE statement:

    this is the SQL-92(i think) stmt I want to run:

    UPDATE XXX.TABLEONE tabOne
    SET tabOne.FIELD1 = tabTwo.FIELD2
    FROM XXX.TABLETWO tabTwo
    WHERE tabOne.pKey = tabTwo.fkey

    In DB2, it appears I have to use different syntax, something more like this:

    UPDATE XXX.TABLEONE tabOne
    SET (tabOne.FIELD1 )
    =
    ( SELECT tabTwo.FIELD2
    FROM XXX.TABLETWO tabTwo
    WHERE tabOne.pKey = tabTwo.fkey
    )


    The problem is, the second UPDATE will update all the rows in TABLEONE since it doesn't have a WHERE clause in the outer update stmt. But since I can only reference TABLETWO in the subselect, I can't get the contraint how I need it. I can't find any examples that do this, they all have constant values in the where clause for the UPDATE.

    Can someone help me out?

    Thanks.
    -Craig

  2. #2
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Nevermind, I figured it out.
    I was wrong about the SQL 92, it looks like DB2 follows the standard and mickeysoft does not (surprise, surprise).

    Anyway.. nevermind.

Posting Permissions

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