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/
