Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2005
    Location
    United States
    Posts
    22

    Unanswered: Sql Update Using Join Or Subquery

    I have seemed to have drawn a blank and I do not know why I cannot perform what appears to be a fundamentally simple update statement.
    Namely: I am operating in a Z/OS version 7 DB2 environment and am attempting to do an update using either an inner join or correlated query.

    Example:
    ========
    Let us assume that we have two similarly structured
    tables in the same subsystem but under two different databases:
    AAA.TASKTBL unique key is made up of one column: TASK_CD
    BBB.TASKTBL unique key is made up of one column: TASK_CD

    I want to update a non-key column (TASK_DESC) in AAA.TASKTBL to its
    counterpart in BBB.TASKTBL, based on the respective
    TASK_CD values being equal.

    I have tried this and it failed:

    UPDATE AAA.TASKTBL A,
    BBB.TASKTBL B
    SET A.TASK_DESC = B.TASK_DESC
    WHERE A.TASK_CD = B.TASK_CD

    likewise this fails:

    UPDATE AAA.TASKTBL A
    SET A.TASK_DESC =
    (SELECT B.TASK_DESC FROM BBB.TASKTBL B
    WHERE B.TASK_CD = A.TASK_CD)

    anyone's assistance will be greatly appreciated

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The second one should work. What error message are you getting? Did you look up the error message in the Message Reference manual?

    If the error is that you are getting multiple rows in the subselect, then this might work:

    UPDATE AAA.TASKTBL A
    SET A.TASK_DESC =
    (SELECT B.TASK_DESC FROM BBB.TASKTBL B
    WHERE B.TASK_CD = A.TASK_CD fetch first 1 rows only)

    Or you may have gotten a SQL0407N (Assignment of a NULL value to a NOT NULL column) which means that the corresponding row on the B table could not be found and DB2 tried to use a null value for the update. To fix that problem, try this:

    UPDATE AAA.TASKTBL A
    SET A.TASK_DESC =
    (SELECT B.TASK_DESC FROM BBB.TASKTBL B
    WHERE B.TASK_CD = A.TASK_CD fetch first 1 rows only)
    where exists (SELECT 1 FROM BBB.TASKTBL B WHERE B.TASK_CD = A.TASK_CD)

    Edit: The above error message -407 is from DB2 LUW, not OS/390, so it might be a little different (or it might be the same).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2006
    Posts
    82
    Sql Update Using Join Or Subquery
    I suppose this is not supported in DB2 V7. If am right in order to use that you have to be in atleast V8.1 New function Mode. Correct me if am wrong

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by db2dcs
    I have seemed to have drawn a blank and I do not know why I cannot perform what appears to be a fundamentally simple update statement.
    Namely: I am operating in a Z/OS version 7 DB2 environment and am attempting to do an update using either an inner join or correlated query.

    Example:
    ========
    Let us assume that we have two similarly structured
    tables in the same subsystem but under two different databases:
    AAA.TASKTBL unique key is made up of one column: TASK_CD
    BBB.TASKTBL unique key is made up of one column: TASK_CD

    I want to update a non-key column (TASK_DESC) in AAA.TASKTBL to its
    counterpart in BBB.TASKTBL, based on the respective
    TASK_CD values being equal.

    I have tried this and it failed:

    UPDATE AAA.TASKTBL A,
    BBB.TASKTBL B
    SET A.TASK_DESC = B.TASK_DESC
    WHERE A.TASK_CD = B.TASK_CD
    This can't be done because the table you want to update is read-only. The table we have here is is a joined table of AAA.TASKTBL and BBB.TASKTBL. Therefore, you have to use a non-read-only (updatable) table for the UPDATE statement, e.g. don't use joins.

    UPDATE AAA.TASKTBL A
    SET A.TASK_DESC =
    (SELECT B.TASK_DESC FROM BBB.TASKTBL B
    WHERE B.TASK_CD = A.TASK_CD)
    As Marcus said, this should work, assuming that (a) DB2 for z/OS V7 supports such subselects, and (b) your subselect is a scalar subselect and returns a single row/value only.

    Also note that your update statement will update every row in AAA.TASKTBL and not only those for which a row in BBB.TASKTBL exists. So your both statements do have quite different semantics. To fix that, you'd have to add a WHERE clause for the UPDATE itself:
    Code:
    UPDATE AAA.TASKTBL AS A
    SET    A.TASK_DESC = ( SELECT B.TASK_DESC
                           FROM   BBB.TASKTBL AS B
                           WHERE  B.TASK_CD = A.TASK_CD )
    WHERE  EXISTS ( SELECT 1
                    FROM   BBB.TASKTBL AS B
                    WHERE  B.TASK_CD = A.TASK_CD )
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by db2dcs
    UPDATE AAA.TASKTBL A
    SET A.TASK_DESC =
    (SELECT B.TASK_DESC FROM BBB.TASKTBL B
    WHERE B.TASK_CD = A.TASK_CD)
    the above statement might fail due to the correlation character in the update clause. Simply omit it.

    UPDATE AAA.TASKTBL A
    SET TASK_DESC =
    (SELECT B.TASK_DESC FROM BBB.TASKTBL B
    WHERE B.TASK_CD = A.TASK_CD)

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by umayer
    the above statement might fail due to the correlation character in the update clause. Simply omit it.

    UPDATE AAA.TASKTBL A
    SET TASK_DESC =
    (SELECT B.TASK_DESC FROM BBB.TASKTBL B
    WHERE B.TASK_CD = A.TASK_CD)
    That shouldn't be a problem at all.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by stolze
    That shouldn't be a problem at all.

    a correlation name in the assignment clause is not supported in DB2 V7

    ( and in V8 CM it depends on the PTFs applied )

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Thanks. I missed that we are talking about V7 here.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Feb 2005
    Location
    United States
    Posts
    22

    thanks for everyone's help

    Thanks to Marcus and others who advised me to stick with the sub-query approach. And thanks to Umayer who advised me to remove the correlation character. Both pieces of advice helped to me get my update to work.

    In actuality, my chore is much more complex than the simple example that I posted. It involves multiple columns being updated based on multiple conditions; and all from within a tricky cursor. But I was able to apply your collective advice to my complicated situation and so far my testing has produced the expected results based on everyone's assistance.

    Again much thanks to all of you. It is forums like this, and people like you, that provide mankind with excellence.

    regards
    db2dcs

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    For UPDATE, you could set multiple columns at once:
    Code:
    UPDATE table
    SET (col1, col2, col3) = ( SELECT x, y, z FROM other_table )
    WHERE ...
    Also, if you don't have too complex computations in your cursor, it may be worthwhile to push the complete cursor stuff to DB2. But then we would need some more details on that logic.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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