|
In examining your statement, I noticed that you are not specifying selection (i.e. Where clause) on the update statement. As a result, you are requesting that every row in the Z9519 table be updated by DB2 as a result of this request. If the subselect does not produce a match for that row in Z9519, DB2 has to update to the null value (in which case the COALESCE function never even gets executed for that row, because the subselect didn't return a row).
If you want to update only a subset of the Z9519 rows (those that have a matching values for columns ZADESE1 and WHS) then you need to add that selection to the update statement:
UPDATE Z9519 A SET A.ZORD#T =
(SELECT B.ZORD#T FROM Z9517 B WHERE A.ZADES1 = B.ZADES1 AND A.WHS=B.WHS)
WHERE EXISTS (SELECT 'x' from Z9517 BB where A.ZADES1 = BB.ZADES1 AND A.WHS=BB.WHS)
OR
UPDATE Z9519 A SET ZORD#T =
CASE
WHEN (SELECT B.ZORD#T FROM Z9517 B WHERE A.ZADES1 = B.ZADES1 AND A.WHS=B.WHS) IS NOT NULL
THEN(SELECT BB.ZORD#T FROM Z9517 BB WHERE A.ZADES1 = BB.ZADES1 AND A.WHS=BB.WHS)
ELSE A.ZORD#T
END ==================================
MORE INFORMATION ON THIS TOPIC
==================================
Visit the ITKnowledge Exchange and get answers to your DB2 questions fast.
Check out this Search400.com Featured Topic: Expert advice on DB2
Search400.com's targeted search engine: Get relevant information on DB2/400.
The Best Web Links: Tips, tutorials and more.
|
