Update statement SQL with CTE

I have a common table expression that I am using trying to use as an update statement. The only reason for the CTE is so I can use a where clause to filter by CredCount. I would like to use that where clause to update only records that match in this case CredCount of 2. However, I am having trouble with the update part of the query.

Members_id	Credentials	CredCount	members_amountdue
1	CMA, CPR	2	0
2	CMA, CPR	2	0
3	CMA, CPR	2	0

Here is the query for that

WITH CTE AS (
SELECT members_id,   members_amountdue,  [Credentials], LEN([Credentials]) - LEN(REPLACE([Credentials], ',', '')) + 1 AS CredCount
FROM (
        SELECT DISTINCT  mem1.members_id, mem1.members_amountdue, 
            STUFF(
                (SELECT DISTINCT ', ' + credentials_code
                FROM members AS mem JOIN
                members_credentials  AS mc ON mc.members_id = mem.members_id JOIN
                credentials AS c ON c.credentials_id = mc.credentials_id
                WHERE mem.[members_id] = mem1.[members_id]              
                FOR XML PATH ('')), 1, 1, '')  AS [Credentials]
        FROM members AS mem1 JOIN
        members_status as ms on ms.members_status_id = mem1.members_status_id   
    ) AS derived),
CTE2 AS ( SELECT members_id
            FROM members)
SELECT  CTE.members_id, CTE.Credentials, CTE.CredCount, (CTE.members_amountdue + 25) as NewPriceTotal
FROM CTE JOIN
CTE2 ON CTE.members_id = CTE2.members_id
WHERE CTE.CredCount = 2

With the update statement I was looking at an example provided here at Update records in table from CTE so I added the update statement to the bottom of the query

WITH CTE AS (
SELECT members_id,   members_amountdue,  [Credentials], LEN([Credentials]) - LEN(REPLACE([Credentials], ',', '')) + 1 AS CredCount
FROM (
        SELECT DISTINCT  mem1.members_id, mem1.members_amountdue, 
            STUFF(
                (SELECT DISTINCT ', ' + credentials_code
                FROM members AS mem JOIN
                members_credentials  AS mc ON mc.members_id = mem.members_id JOIN
                credentials AS c ON c.credentials_id = mc.credentials_id
                WHERE mem.[members_id] = mem1.[members_id]              
                FOR XML PATH ('')), 1, 1, '')  AS [Credentials]
        FROM members AS mem1 JOIN
        members_status as ms on ms.members_status_id = mem1.members_status_id   
    ) AS derived),
CTE2 AS ( SELECT members_id
            FROM members)
SELECT  CTE.members_id, CTE.Credentials, CTE.CredCount, (CTE.members_amountdue + 25) as NewPriceTotal
FROM CTE JOIN
CTE2 ON CTE.members_id = CTE2.members_id
WHERE CTE.CredCount = 2
UPDATE members
set members_amountdue = NewPriceTotal

When I add the update statement I get an error for invalid column name for ‘NewPriceTotal’. I know I need the column to match in order for the update to work but I’m not sure why it’s invalid.

Members_id	Credentials	CredCount	NewPriceTotal
1	CMA, CPR	2	25
2	CMA, CPR	2	25
3	CMA, CPR	2	25

I would like for the NewPriceTotal to be set at the members_amountdue from the members table but I’m not sure where I made the wrong turn at. Any help, comments or feedback is greatly appreciated.

Answer

Try this:

....
CTE2 AS ( 
   SELECT members_id
   FROM members
), CTE3 AS (
   SELECT  CTE.members_id, CTE.Credentials, CTE.CredCount,  
           CTE.members_amountdue,  
           (CTE.members_amountdue + 25) as NewPriceTotal
   FROM CTE JOIN CTE2 ON CTE.members_id = CTE2.members_id
   WHERE CTE.CredCount = 2)
UPDATE CTE3
SET members_amountdue = NewPriceTotal