1

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
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.