UPDATE SUM GROUP BY

Question

Going to Update SUM to accountDetail table's totalNumber column from Trans tables totalNumber with matching accountId(column) and assetId(column)

Here is my trying query

UPDATE demo.dbo.AccountDetails
SET totalNumber=(
        SELECT SUM(totalNumber) FROM demo.dbo.Trans
        WHERE AccountDetails.accountId = Trans.accountId and AccountDetails.assetId = Trans.assetId
        GROUP BY accountId 
       )
  WHERE(
        SELECT totalNumber FROM demo.dbo.Trans
        WHERE AccountDetails.accountId = Trans.accountId and AccountDetails.assetId = Trans.assetId
        GROUP BY accountId 
  )

This gives me error The multi-part identifier "AccountDetails.accountId" could not be bound.

note accountId has many assetId and all different

accountDetail table

id  totalNUmber   assetId   accountId 
2   30.0000       1020      789 
3   412.0000      1021      768 
4   159.0000      1020      768 
9   225.0000      1021      789 

Trans table

id  accountid   totalNumber   assetid
5   768         8.000000      1020
6   768         4.000000      1021  
7   789         6.000000      1021  
8   768         -100.000000   1021  
9   768         -89.000000    1021  
10  768         -9.000000     1021
11  769         -7.000000     1020

Answer 1

You can use CTE to group the totalNumber by accountId, assetId then update like below.

;with cte as(
 SELECT accountId, assetId, SUM(totalNumber) as totalNumber
 FROM demo.dbo.Trans
 GROUP BY accountId, assetId 
)

UPDATE a
set a.totalNumber = cte.totalNumber
FROM demo.dbo.AccountDetails a
INNER JOIN cte ON a.accountId = cte.accountId and a.assetId = cte.assetId

Answer 2

Please, check your where statement you forgot add

where totalNumber =