update using sum

Question

I am trying to update the totalNumber column of the AccountDetails table to be the SUM() of the totalNumber column of the Trans table, where the accountID and the assetID columns match.

The is the query I tried using:

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 
  )

Howevery, this gives me the error:

The multi-part identifier "AccountDetails.accountId" could not be bound.

Please note the the accountID can have many assetID and all are 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

did this on notepad, so might have some syntax errors.

UPDATE AD
SET totalNumber = tr.totalNumber
FROM demo.dbo.AccountDetails AD
JOIN (SELECT accountId, assetId, SUM(totalNumber) AS totalNumber 
      FROM demo.dbo.Trans
      GROUP BY accountId, assetId
      ) tr ON tr.accountId = AD.accountId AND tr.assetId = AD.assetId