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

i have table contains distinct name, dept, state worked, , empty field called earnings. have second table has many rows name, dept, , state worked match table , earnings field. i'd run update query total earnings distinct name, dept, state worked records , place 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

update   set a.earning = b.totalearning tablea  inner join  (select name, dept, state , sum(earning) totalearning              tableb               group name, dept, state ) b on  a.name = b.name  , a.dept = b.dept  , a.state = b.state