Update with Sub Sums - 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

General SQL Server Forums

New to SQL Server Programming

Update with Sub Sums

Author

Topic

mkdlmr
Starting Member

21 Posts

Posted - 2013-12-17 : 17:33:44

Hi All,

I'm trying to update a value into a table a sum. The two tables have ID values. These ID values appear once in Table1 and multiple times in Table2. I'm currently trying to sum up the values in Table2 where the IDs are equal to Table1 and then update the value:

UPDATE [Table1] SET
[Total] =
(SELECT SUM([Table2].[QTY]) FROM [Table2],
[Table1] WHERE [Table1].[ID] = [Table2].[ID]
GROUP BY [Table1].[ID])
FROM [Table1], [Table2] WHERE [Table1].[ID] = [Table2].[ID]

Thanks,
Mark

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 17:41:58

UPDATE t1
SET Total = SUM(t2.QTY)
FROM Table1 t1
JOIN Table2 t2 ON t1.ID = t2.ID
GROUP BY t1.ID

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-18 : 06:34:04

[code]
UPDATE t1
SET Total = TotalQty
FROM Table1 t1
JOIN (SELECT ID,SUM(QTY) AS TotalQty
FROM Table2
GROUP BY ID
)t2
ON t1.ID = t2.ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources