To amplify Troy's comments about aggregate updates, it is done in RBase by creating a view, then referencing the view in the update statement. Some other dbms's, such as SQL allows direct embedding, but R:Base needs a view.
CREATE VIEW TotalBudget (Account,Budget) AS + SELECT Account,SUM(Budget) + FROM SourceTable + GROUP BY Account UPDATE BudgetTotals SET TotalBudget = t2.Budget + FROM BudgetTotals t1, TotalBudget t2 + WHERE t1.Account = t2.Account Troy Sosamon <[EMAIL PROTECTED]> wrote: >This is supported in R:base and will work just like you have it. > >update accounts set budget=t2.budget from accounts t1,NewData t2 where >t1.account=t2.account > >The common mistake that people make when doing multi table updates is leaving >the first table out of the from part of the update which will generate an >error, but you have it there. > >The thing that does not work is updating the first table with an aggragate >from the second table like: >update accounts set budget = (sum(t2.somecol)) from budget t1, some table t2 >where ... group by t2.budget >I think it will execute, but you won't get your desired results. > >Troy Sosamon > >>===== Original Message From [EMAIL PROTECTED] ===== >>hello again, im trying to do another sql statement but i've been told its not >possible to do it and wanted to confirm that. >> >>i basicly have 2 tables, �the first table called NewData has account and >budget. �The second table is Accounts and has account, budget and some other >columns that we need to preserve. �What im trying to do is update Accounts >with the new budgets from NewData. � If in NewData there is >>account 3 and budget $5.00 then i need that to update account 3 in Accounts >to have a budget of $5.00. �There are a few thousand of these entires in >NewData and i was hoping there was some way to do it without a cursor loop. � >Something like: >> >>update accounts set budget=t2.budget from accounts t1,NewData t2 where >t1.account=t2.account >> >>what i heard exactly was "in standard SQL, multi-table update does not >exist." >> >>any ideas? (: > >Troy Sosamon >Denver Co >[EMAIL PROTECTED] > >================================================ >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/ > -- Albert Berry Full Time Consultant to PSD Solutions 350 West Hubbard, Suite 210 Chicago, IL 60610 312-828-9253 Ext. 32 __________________________________________________________________ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.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/
