Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Updating a field in the table with duplicate records

Kent Milligan
By
Published: 31 May 2004

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.

Dig Deeper on DB2 UDB (universal databases)

SearchDataCenter

Close