I keep getting the error message of null values not allowed in column or variable ZORD#T for SQL update (using V5R3):
UPDATE Z9519 A SET A.ZORD#T = (SELECT COALESCE( B.ZORD#T, A.ZORD#T ) FROM Z9517 B WHERE A.ZADES1 = B.ZADES1 AND A.WHS=B.WHS)
While I have no problem with the same tables, same SQL in V5R2 (I already set 0 to all numeric fields in table Z9519 before executing SQL update and all numeric fields in Z9517 are not NULL). Can you offer some suggestions as to why I'm having this problem?
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)
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
Dig Deeper on DB2 UDB (universal databases)
Related Q&A from Kent Milligan
Create a host variable of the where in statement on the fly with dynamic SQL. Continue Reading
To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor ... Continue Reading
When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC ... Continue Reading