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

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).

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

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.

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:

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.

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

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

Thanks. I missed that we are talking about V7 here.

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

For UPDATE, you could set multiple columns at once:
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.