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!
I'm an Australian Chief Analytics Officer passionate about data science, visual insights, and all things sport—particularly cricket. An adventurer at heart, I’ve gone from abseiling cliffs to snorkeling in crystal-clear waters, sleeping in the wilds of Africa, and exploring destinations worldwide, with my latest trip taking me to Bali. When I'm not diving into data or analytics, I'm spending time with my three sons and two daughters, attempting to hit sixes for my local cricket club, reviewing chicken schnitzels or honing my craft around a coffee machine.
Really, really thank you!
Absolute pleasure!