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.IDBut 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!
Really, really thank you!
Absolute pleasure!