************************** Beginning of Data *********************************
0001.00 UPDATE lvivqry/initp100 0002.00 set initp100.audus = 0003.00 (select distinct coalesce (iupd12.loaddateb, initp100.audus) 0004.00 from lvivqry/iupd12 where 0005.00 iupd12.userid = initp100.audus and 0006.00 iupd12.house = initp100.house and 0007.00 iupd12.aisle2 = initp100.aisle and 0008.00 iupd12.prdno = initp100.prdno and 0009.00 iupd12.stdate = initp100.ittdt and 0010.00 iupd12.d4qtyu = initp100.quant ) 0011.00 where initp100.tcode = 'SS' **************************** End of Data ************************************
The problem is that all of the rows that you're trying to update in the audus table don't have a matching row when you join the tables on the Set clause. When there is no match on the join, then the SET clause tries to assign a null value to that row in the audus table.
You need add additional selection (the same join logic) to the WHERE clause of the UPDATE statement to ensure that only those rows with a matching join row get updated. Something like:
UPDATE lvivqry/initp100
set initp100.audus =
(select distinct iupd12.loaddateb
from lvivqry/iupd12 where
iupd12.userid = initp100.audus and
iupd12.house = initp100.house and
iupd12.aisle2 = initp100.aisle and
iupd12.prdno = initp100.prdno and
iupd12.stdate = initp100.ittdt and
iupd12.d4qtyu = initp100.quant )
where initp100.tcode = 'SS'
AND EXISTS (select 'X' from from lvivqry/iupd12 where
iupd12.userid = initp100.audus and
iupd12.house = initp100.house and
iupd12.aisle2 = initp100.aisle and
iupd12.prdno = initp100.prdno and
iupd12.stdate = initp100.ittdt and
iupd12.d4qtyu = initp100.quant ) ==================================
MORE INFORMATION ON THIS TOPIC
==================================
Check out this Search400.com Featured Topic: Database issues resolved
Search400.com's targeted search engine: Get relevant information on DB2/400.
The Best Web Links: Tips, tutorials and more.
This was first published in May 2004