I do this by creating a temporary view, then performing the update
from the view.
Create Temp View TmpVue (PK, SUM) AS +
select PK, SUM(whatever) +
FROM Table2 GROUP BY PK
UPDATE Table1 SET Column = t2.SUM +
FROM Table1 t1, Table2 t2 +
WHERE Table1.PK = Table2.PK
This works faster than I can type the instructions.
Albert
On 12/09/2010 9:25 AM, John Engwer wrote:
Is it possible to use the update command to update TABLE1 using the
sum of values from TABLE2?
Such as… UPDATE TABLE1 SET InStockQty = (SUM)T2.OnHand FROM TABLE1
T1,TABLE2 T2 WHE T1.MfgStyle = T2.StockNo
I think that I used this method in the past but I can’t make it work
now. I probably have the syntax wrong. I tried working a sub-select
into the sequence but I could not get that to work either.
For now, I have the update as part of a CURSOR and I go through the
table one record at a time; use a select to capture the sum value;
then use the update command.
John
--- RBASE-L
=======================3D=======================3
D=
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
=======================3D=======================3
D=
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
=======================3D=======================3
D=
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
=======================3D=======================3
D=
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
=======================3D=======================3
D=
