CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alan Peterson
Alan Peterson

Reputation: 1

SQL Update Query to update a table with aggregate value from another table using multiple fields

I have table A which contains distinct Name, Dept, State Worked, and an empty field called earnings. I have a second table which has many rows where the Name, Dept, and State Worked match table A and an earnings field. I'd like to run an update query that will total the earnings for the distinct Name, Dept, State Worked records and place that value in earnings field in table A.

Table A:
Alan, Sales, CO
Alan, Sales, CA
Paul, Development, CO
Paul, Development, CA

Table B:
Alan, Sales, CO, $100
Alan, Sales, CO, $150
Alan, Sales, CA, $200
Paul, Development, CO, $100
Paul, Development, CA, $200
Paul, Development, CA, $300

Desired Result (Table A):
Alan, Sales, CO, $250
Alan, Sales, CA, $200
Paul, Development, CO, $100
Paul, Development, CA, $500

Upvotes: 0

Views: 199

Answers (1)

M.Ali
M.Ali

Reputation: 69524

UPDATE A 
 SET A.Earning = B.TotalEarning
FROM tableA A 
INNER JOIN  (SELECT Name, Dept, State , SUM(Earning) TotalEarning
             FROM TableB 
             GROUP BY Name, Dept, State ) B
ON  A.Name = B.Name 
AND A.Dept = B.Dept 
AND A.State = B.State

Upvotes: 2

Related Questions