Something like this -
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 )
WHERE EXISTS
(SELECT f1.glco
FROM F0911 f1
WHERE f1.glco = '00900' and
f1.gldct = 'JE' and
f1.glicut = 'G' and
f1.glaa > 0 and
f1.glasid <> ' '
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 )
John Arnold
(301) 354-2939
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tomasz Skorza
Sent: Thursday, April 24, 2008 7:32 AM
To: midrange-l@xxxxxxxxxxxx
Subject: 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)
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
