Updating a field in the table with duplicate records
How can I in field from another table with SQL iSeries? I used follow SQL, but system found mistakes: Null values not allowed in column or variable AUDUS. When I run Select only system works correctly.
************************** 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.