How to change select to update

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)