Update value from SUM(values) in another table

Update value from SUM(values) in another table

I recently found myself forgetting the exact syntax to update a value in a table based on the sum of another set of values in another. You cannot, for example, do this:

UPDATE m
SET m.Foo = SUM(s.valsum)
FROM [MASTER] m
INNER JOIN [Foos] s ON s.ID = m.ID

But you can do it with the following sub-query:

UPDATE m
 SET m.Foo = f.valsum
 FROM [MASTER] m
 INNER JOIN
 (
   SELECT ID, CCY, SUM(val) valsum
   FROM [Foos]
   GROUP BY ID, CCY
 ) f ON m.ID = f.ID AND m.CCY = f.CCY;

I hope this helps!

Mike250

Australian. Sport. Passionate Cricket Fan. Go Pro. Abseiling. Snorkeling. Travel. Golf R. SQL Server Developer. Three sons. One daughter. Last Trip: New York.

Related Posts
2 Comments
    • Fernando Morais
    • On: August 9, 2018

    Really, really thank you!

    Reply
Leave a comment

Your email address will not be published. Required fields are marked *