Update Help - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Update Help

Author

Topic

vk18
Posting Yak Master

146 Posts

Posted - 2006-10-18 : 12:56:30

Hello Friends,

I am trying to update a table based on another table values, and i am using aggregate function in that. It is not allowing me to use aggregate in update. how to deal with this.
Thx

Update tbldetail set ActualTotals = sum(tblspot.rateactual) as rateactual from tbldetail inner join tblspot on tbldetail.pkid = tblspot.fkdetail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-18 : 13:51:48

[code]Update d
set d.ActualTotals = x.rateactual
from tbldetail d
inner join (
SELECT fkdetail,
SUM(rateactual) rateactual
FROM tblspot
GROUP BY fkdetail
) x ON x.fkdetail = d.pkid [/code]

Peter Larsson
Helsingborg, Sweden

vk18
Posting Yak Master

146 Posts

Posted - 2006-10-18 : 14:08:53

Thx man Appreciate it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-18 : 14:12:32

Also try this and compare performance

Update		d
set		d.ActualTotals = (SELECT SUM(s.rateactual) FROM tblspot s WHERE s.fkdetail = d.pkid)
from		tbldetail d

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources