If you use NoteTab here are two clips I created to do exactly that.
H="UPDATE - 2 table simple"
UPDATE ^?[Table 1 name] SET +
^?[Table 1 col to update] = t2.^?[Table 2 data col] +
FROM ^?[Table 1 name] t1,^?[Table 2 name] t2 +
WHERE t2.^?[Table 2 linking col] = t1.^?[Table 1 linking col] +
AND
H="UPDATE - 2 table sum"
UPDATE ^?[Table 1 name] SET +
^?[Table 1 col to update] = (t1.^?[Table 1 col to update] + t2.^?[Table 2 col to add]) +
FROM ^?[Table 1 name] t1,^?[Table 2 name] t2 +
WHERE t2.^?[Table 2 linking col] = t1.^?[Table 1 linking col] +
AND
If you don't have NoteTab this translates to:
UPDATE Table1Name SET +
Table1Col2Update = (t1.Table1Col2Update + t2.Table2Col2Add) +
FROM Table1Name t1,Table2Name t2 +
WHERE t2.Table2LinkingCol = t1.Table1LinkingCol +
AND
or in J's case:
UPDATE stmtbill SET +
col1 = (sum(t1.ar)) +
from stmtbill t1,stmtdet t2 +
where t1.famno = t2.famno +
and t2.invdate > (.#date -30)
J, check my imputed table designations are correct.
At 14:21 01/11/02 +0000, you wrote:
Warmest regards,I think you have to do it in two steps (I don't think aggregate functions can be used w/ multi table updates). I wasn't sure which columns belonged to which tables so look this over carefully:Create Temp View FamTtl (FamNo, FamTtl) as + sel FamNo, (sum(AR)) + from StmtDet + group by FamNo + whe InvDate > (.#date - 30) Update StmtBill + Set Col1= t3.FamTtl + from StmtBill t1, + FamTtl t2 + where t1.FamNo = t2.FamNo Ben Petersen On 1 Nov 2002, at 16:22, J BLAUSTEIN wrote: > I am trying to update a column from another table sum without using a > view - I used: update stmtbill set col1= (sum(ar)) from stmtbill t1, > stmtdet t2 where t1.famno = t2.famno + > and invdate > (.#date -30) > any help would be appreciated > J > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In > the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
Tom Grimshaw
coy: Just For You Software
tel: 612 9552 3311
fax: 612 9566 2164
mobile: 0414 675 903
post: PO Box 470 Glebe NSW 2037 Australia
street: 3/66 Wentworth Park Rd Glebe NSW 2037
email: [EMAIL PROTECTED]
web: www.just4usoftware.com.au
"... the control of impulse -- is the first principle of civilization."-- Will Durant,
Pulitzer Prize winning philosopher, writer and historian
the most needed product in the world can be found at
www.thewaytohappiness.org
This email and any files transmitted with it are confidential to the intended recipient and may be privileged. If you have received this email inadvertently or you are not the intended recipient, you may not disseminate, distribute, copy or in any way rely on it. Further, you should notify the sender immediately and delete the email from your computer. Whilst we have taken precautions to alert us to the presence of computer viruses, we cannot guarantee that this email and any files transmitted with it are free from such viruses.
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
