How to change select to update

[Tomasz Skorża <t.skorza@xxxxxxxxxxx>]

Hi

 

I have following SELECT statement:

 

select f1.gldoc, f1.gldct, f1.glicu, f1.glaa,                  

f1.glasid, f1.glicut, f1.glco, f2.glasid, f2.glaa              

from f0911 f1, f0911la f2                                      

where                                                          

f1.glco = '00900' and                                          

f1.gldct = 'JE' and                                            

f1.glicut = 'G' and                                            

f1.glaa > 0 and                                                

f1.glasid <> ' '                                               

and                                                            

f1.glco =  f2.glco and                                         

f1.gldct = f2.gldct and                                        

f1.glicut =f2.glicut and                                       

f1.glaa = f2.glaa *-1 and                                      

f1.globj <> f2.globj and                                       

f2.glasid = ' ' and                                            

f1.gldoc = f2.gldoc and                                        

f1.glicu = f2.glicu          

 

 

F0911 - phisical

F0911LA - logical

 

Result

 

Document  Do    Batch          Amount                 Serial              Bth   Co           Serial                     Amount     

 Number   Ty   Number                                 Number              Ty                 Number                                

--------  --  --------  --------------------   --------------------       ---  -----  --------------------       ------------------

 6002763  JE    565441             3,820,853   00000011                   G    00900                                        3,820,853-

 3000041  JE     49343             2,690,310   00001422                   G    00900                                        2,690,310-

 3000053  JE     52889            47,246,600   00001420                   G    00900                                       47,246,600-

 5001134  JE    285778                45,000   00001683                   G    00900                                           45,000-

 4000505  JE    136322               868,200   00002032                   G    00900                                          868,200-

 4000505  JE    136322               868,200   00002033                   G    00900                                          868,200-

 4000679  JE    153488            11,609,280   00002049                   G    00900                                       11,609,280-

 5000858  JE    268811            14,464,092   00003637                   G    00900                                       14,464,092-

 6003535  JE    633134             4,581,316   00005166                   G    00900                                        4,581,316-

 4000032  JE     70248               283,000   00001499                   G    00900                                          283,000-

 

 

 

Now I would like to update blank field "Serial number" from F0911LA with values from F0911 (run statement "set f2.glasid = f1.glasid)

 

But how to change this select to update?

 

With one selected record it works but how to do it for every records?

 

Statement for one-row update:

 

update f0911la f2                                     

set f2.glasid = 

(

select f1.glasid from F0911 f1       

where                                                 

f1.glco = '00900' and                                 

f1.gldct = 'JE' and                           

f1.glicut = 'G' and                           

f1.glaa > 0 and                               

f1.glasid <> ' '                              

and                                           

f1.glco =  f2.glco and                        

f1.gldct = f2.gldct and                       

f1.glicut =f2.glicut and                      

f1.glaa = f2.glaa *-1 and                     

f1.globj <> f2.globj and                      

f2.glasid = ' ' and                           

f1.gldoc = f2.gldoc and                       

f1.glicu = f2.glicu and                       

f1.gldoc = 4000535                            

)                                             

where                                         

f2.glco = '00900' and                        

f2.gldct = 'JE' and                          

f2.glicut = 'G' and                          

f2.glaa < 0 and                              

f2.glasid = ' ' and                          

f2.gldoc = 4000535                           

 

 

Thanks for any ideas.

 

--

tomek (JDE World)