update subquery

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  January 23, 2006 at 2:55 pm

  #68066

  Guys

  I have the following 2 queries

  1st Query

  _______________

  select account_num, balance from account

  order by account_num

  2nd Query

  _______________

  select sum(amount), account_num from atransaction

  group by account_num

  order by account_num

  I want the balance field in account table be update with the value of sum(amount) from the second query. Below update statement doesnt seem to work because of the group by clause.

  update account

  set balance = sum(a.amount)

  from atransaction a inner join account c

  on a.account_num = c.account_num

  group by a.account_num

  Any suggestions how this update statement should be written would help

  Thanks

• David.Poole

  SSC Guru

  Points: 76001

  More actions

  January 23, 2006 at 3:02 pm

  #616544

  UPDATE dbo.account

  SET dbo.account.balance = DT.TotalAmount

  FROM dbo.account

  INNER JOIN (

  select sum(amount) AS TotalAmount, account_num from atransaction

  group by account_num

  ) AS DT

  ON dbo.account.account_num = DT.account_num

  LinkedIn Profile