Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update one table based upon SUM(values) in another table on multiple criteria

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(...)

like image 489
BlueChippy Avatar asked Dec 11 '12 10:12

BlueChippy


People also ask

How can I update data from one table to another table?

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.

How do you update one table field from another table in SQL?

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.

How do you update multiple columns in SQL with different conditions?

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.


1 Answers

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;
like image 173
Mahmoud Gamal Avatar answered Sep 21 '22 14:09

Mahmoud Gamal