Q

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.

This was first published in May 2004

Dig deeper on DB2 UDB (universal databases)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchEnterpriseLinux

SearchDataCenter

Close