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!

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

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.

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,

No it is not.

Andy

This will not work in version 7, but will work in 8 or above (as indicated by tonkuma).

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

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)