Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    102

    Unanswered: Mainframe:Join in UPDATE statement

    I'm in a mainframe environment. Using IBM DB2.

    Below are the tables and columns I am working with:
    Table A: Key1, Points
    Table B: Key1, Key2
    Table C: Key2, Points

    I would like to update the Points column in Table A with the corresponding value from Table C.
    However, since there is not a direct key which relates Tables A & C, I will have to do a join which relates all three tables.
    Initially I tried the following:

    UPDATE A
    SET A.Points = C.Points
    FROM A A
    JOIN B B
    ON A.Key1 = B.Key1
    JOIN C C
    ON B.Key2 = C.Key2


    However, I get the error :
    SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD FROM. TOKEN ( .
    MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE WAS EXPECTED


    I assume this is because a FROM statement is not allowed in this mainframe environment.

    Anyone have any insight or suggestions for a workaround ?


    Thanks in advance!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not know mainframe at all but this should work since it is standard SQL:

    update a set points = (select c.points from b inner join c on (b.key2 = c.key2) where b.key1 = a.key1)

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The scalar-fullselect(Like in Andy's example) was supported on DB2 for z/OS Version 8 or later by looking into Manuals "SQL Reference" of Version 7/8/9.

  4. #4
    Join Date
    Sep 2003
    Posts
    102
    Quote Originally Posted by ARWinner
    I do not know mainframe at all but this should work since it is standard SQL:

    update a set points = (select c.points from b inner join c on (b.key2 = c.key2) where b.key1 = a.key1)

    Andy
    update a
    set points =
    (select c.points
    from b inner join c on (b.key2 = c.key2)
    where b.key1 = a.key1)

    Hi,


    I'll try the above SQL out; but my inital questions are:

    1) will they be able to process what "a.key1" ? Isn't table a out of scope ?


    thanks,

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by ontheDB
    1) will they be able to process what "a.key1" ? Isn't table a out of scope ?


    thanks,
    No it is not.

    Andy

  6. #6
    Join Date
    Dec 2008
    Posts
    76
    This will not work in version 7, but will work in 8 or above (as indicated by tonkuma).
    RD

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I don't know why it wouldn't work in 7, since it worked in 4, 5 & 6 and works agin in 8 & 9.
    Dave

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The scalar-fullselect(Like in Andy's example) was supported on DB2 for z/OS Version 8 or later by looking into Manuals "SQL Reference" of Version 7/8/9.
    Sorry, I was wrong.
    DB2 for z/OS Version 7 doesn't support (scalar-fullselect) as a general expression.
    But, it supports (scalar-fullselect)/(row-fullselect) in UPDATE statement.

    This is a simplified syntax of UPDATE statement:
    searched update:
    UPDATE table-name | view-name [correlation-name] SET assignment-clause [WHERE search-condition] ...

    assignment-clause:
    column-name = expression | NULL | (scalar-fullselect)
    or
    (column-name[, column-name[...]]) = (expression | NULL[, expression | NULL[...]]) | (row-fullselect)

Posting Permissions

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