I can't seem to find out how to do this and not sure exactly how to search for it!
I have a table [MASTER]
:
ID varchar(6)
CCY varchar(3)
Val1 decimal(20,5)
Val2 decimal(20,5)
FOO decimal(20,5)
and another table [FOOS]
ID varchar(6)
CCY varchar(3)
Val decimal(20,5)
MASTER
contains one row per ID/CCY composite key (not sure if thats correct term) e.g.
ABCDEF GBP 200.00 100.00 null
ABCDEF EUR 400.00 150.00 null
ZYXWVU GBP 300.00 200.00 null
ZYXWVU EUR 400.00 200.00 null
FOOS
contains multiple rows and DOES NOT contain a row for every MASTER
e.g.
ABCDEF GBP 50.00
ABCDEF GBP 51.00
ABCDEF GBP 150.00
ZYXWVU GBP 100.00
ZYXWVU EUR 200.00
ZYXWVU EUR 400.00
I'd like to run a query to update only matching MASTER
rows with SUM(FOOS.Val)
. e.g.
ABCDEF GBP 200.00 100.00 251.00
ABCDEF EUR 400.00 150.00 null
ZYXWVU GBP 300.00 200.00 100.00
ZYXWVU EUR 400.00 200.00 600.00
...but although I've tried a numer of options (where exists
, inner join
) I can't seem to be able to either link to a single MASTER
or do the SUM(...)
We can update the table using UPDATE statement in SQL. The update statement is always followed by the SET command. The SET command is used to specify which columns and values need to be updated in a table.
In such a case, you can use the following UPDATE statement syntax to update column from one table, based on value of another table. UPDATE first_table, second_table SET first_table. column1 = second_table. column2 WHERE first_table.id = second_table.
To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column.
Try this solution:
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With