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 ************************************  

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.