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
Best Answer
did this on notepad, so might have some syntax errors.