update total value with values from other table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

update total value with values from other table

Author

Topic

christina_rules
Starting Member

23 Posts

Posted - 2009-08-10 : 01:26:31

I want to update the total rate of a code in tableA based on the each rate in tableB having the same code. These two tables are linked.

Below script is what I've done but only for a single record. How can I do something like this for multiple records?

update tableA
set sum_rate =(select sum(single_rate) from tableB where tableB.code = 'A')
where tableA.code = 'A'

For example what I want is to have a sql statement that can update the total sum of the rates in tableA by searching through tableB.

Thanks.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-10 : 02:10:56

try this

update a set a.sum_rate=tab.sum_rate from tableA a inner join
(select code,sum(single_rate) as sum_rate from tableB group by code) as tab
on a.code=tab.code

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources