Update one field with Sum from the 2nd 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 2000 Forums

SQL Server Development (2000)

Update one field with Sum from the 2nd table

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-04-14 : 15:37:43

Hi, Please somebody give me a hand!

Typically, I use following to update one table with values from another:

Update tblA set tblA.fld1 = tblB.fld1 where tblA.key1=tblB.key1
or

Update tblA set tblA.fld1 = tblB.fld1
from tblA join tblB on tblA.key1=tblB.key1
Where...

Now I need to update tblA.fld1 with value from sum(tblB.fld1) group by tblB.fldKey where tblA.fldKey = tblB.fldKey.

I have tried cuople ways, but haven't been able to get it streight.
here is one:
update mySummary set QTY_ThisMonth =
select Item, Sum(Quantity)
from MonthEndInventory
where convert(varchar(10), EnteredDate, 101)= '02/28/2006'
group by Item

Basically, I am struggling on arrange sum, where, group by and join in the right order to get the result.

I will make up some sample data if it is necessary.

Thanks!

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-04-14 : 16:28:14

When I was making up some sample data to post here, I got my answer.

update mySummary set QTY_ThisMonth = b.ThisMonth from mySummary join
(select sum(Quantity) as ThisMonth, Item from myDetail group by Item) b
on mySummary.Item=b.Item

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources