Re: problem with update from select

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

Well, you can always throw a DISTINCT in the main select - you have to 
have a single row returned in order to use it for update - makes sense? 
Can't update a single value with multiple values.

HTH
Vern

Tomasz Skorża wrote:
> Hi guys
>
> I have one file (JDE) F0901 where many fields exist but I'm interested 
> in 4 fields:
> GMMCU, GMOBJ, GMSUB - this is key
> and data GMR001
> (field GMMCU contain data right adjusted like '         500')
>
> and I have temporary file F0901T with "source of update", there are only 
> 4 fields
> GMMCU, GMOBJ, GMSUB, GMR001
> like this
>          Business     Obj      Sub     CAT  
>            Unit       Acct              1   
>        ------------  ------  --------  ---  
> 000001 500           1610    H50000    MED  
> 000002 500           1610    4260500   MED  
> 000003 500           1612    H50000    VAC  
> (field GMMCU in second file contain data left adjusted like '500 
>            ')
>
> Now I would like to update GMR001 in F0901 using data from F0901T
>
> prepare this SQL code
>
> update F0901 F01                                          
> set F01.GMR001 = (                                        
> select F02.GMR001                                         
> from F0901T F02                                     
> where                                                     
> ltrim(rtrim(f01.gmmcu))=ltrim(rtrim(f02.gmmcu)) and       
> f01.gmobj=f02.gmobj and                                   
> f01.gmsub=f02.gmsub)                                      
> where                                                     
> ltrim(rtrim(F01.gmmcu))!!F01.gmobj!!F01.GMSUB in (        
> select ltrim(rtrim(F02.gmmcu))!!F02.gmobj!!F02.GMSUB      
> from F0901T F02)                                    
>                            
> But when I calling I get error
> Result of SELECT more than one row.    
>  Message ID . . . . . . :   SQL0811   
>
> How to change the sql code to do it correctly?                             
>
> Regards
>
> Tomek
>
>