What is wrong with this CTE

Question

I'm pretty new to CTE's so I hope the problem is obvious with this one:

WITH cteTotal(TransactionTotal) AS
    (
    SELECT
        SUM(Amount) as TransactionTotal
    FROM
        #NewDetails
    )
    UPDATE
        FinancialAccounts
    SET
        Balance = Balance + cteTotal.TransactionTotal
    WHERE
        AccountID = @AccountID;

The error message I get is

The multi-part identifier "cteTotal.TransactionTotal" could not be bound.

Answer 1

You need to bring the CTE in to your query.

Just to be sure #NewDetails will only contain details for one transaction? Or at least to only 1 AccountID?

You can do away with the CTE

update financialaccounts set balance = balance + (select sum(amount) from #NewDetails) where accountid = @AccountID

Answer 2

Try something like this:

WITH cteTotal(TransactionTotal) AS
(
     SELECT
         SUM(Amount) as TransactionTotal
     FROM
         #NewDetails
)
UPDATE
         fa
SET
         Balance = Balance + cteTotal.TransactionTotal
from FinancialAccounts fa, cteTotal
WHERE
         AccountID = @AccountID;

Answer 3

Hi @RogerSchlueter-7899 ,

Welcome to Microsoft Q&A!

The multi-part identifier "cteTotal.TransactionTotal" could not be bound.

You could consider cteTotal as a temporary table and you also need to include the cteTotal in the update statement, otherwise the sql server could not recognize where TransactionTotal is from.

Please also refer below:

 WITH cteTotal(TransactionTotal) AS  
     (  
     SELECT  
         SUM(Amount) as TransactionTotal  
     FROM  
         #NewDetails  
     )  
     UPDATE  
         FinancialAccounts  
     SET  
         Balance = Balance + (select TransactionTotal from cteTotal)  
     WHERE  
         AccountID = @AccountID;  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.