Updating One Table Based on Sum of Another

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

General SQL Server Forums

New to SQL Server Programming

Updating One Table Based on Sum of Another

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2010-02-06 : 16:41:49

I'm trying to calculate a column in one table based on another table. I've tried the following, with the following error.

UPDATE b set b.OrderWeight = SUM(a.LineWeight)
FROM OrderDetail a
JOIN OrderHeader b on a.TransId = b.TransId
GO

Error message: "Msg 157, Level 15, State 1, Line 1
An aggregate may not appear in the set list of an UPDATE statement."

Here is a representation of the tables involved:

OrderHeader table:

TRANSID|ORDERWEIGHT
-------------------
0001 |NULL
0002 |NULL

OrderDetail table:

TRANSID|ENTRY|LINEWEIGHT
------------------------
0001 |001 |5
0001 |002 |11
0002 |001 |33

After the SQL statement, I would like the OrderHeader table to look
like this:

OrderHeader table:

TRANSID|ORDERWEIGHT
-------------------
0001 |16
0002 |33

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-06 : 16:51:57

Try this:


UPDATE oh
SET OrderWeight = dt.OrderWeight
FROM OrderHeader oh
JOIN 
(
	SELECT a.TransId, SUM(a.LineWeight) AS OrderWeight
	FROM OrderDetail a
	JOIN OrderHeader b 
	ON a.TransId = b.TransId 
	GROUP BY a.TransId
) dt
ON oh.TransId = dt.TransId

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

BobRoberts
Posting Yak Master

109 Posts

Posted - 2010-02-06 : 17:12:31

Yes, that works beautifully - you've saved my Monday!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-06 : 17:23:06

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources