title: Update statement SQL with CTE
body: <p>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.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Members_id</th>
  <th>Credentials</th>
  <th>CredCount</th>
  <th>members_amountdue</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>CMA, CPR</td>
  <td>2</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2</td>
  <td>CMA, CPR</td>
  <td>2</td>
  <td>0</td>
  </tr>
  <tr>
  <td>3</td>
  <td>CMA, CPR</td>
  <td>2</td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Here is the query for that</p>
  <pre><code>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
  </code></pre>
  <p>With the update statement I was looking at an example provided here at <a href="https://stackoverflow.com/questions/11562536/update-records-in-table-from-cte?noredirect=1&amp;lq=1">Update records in table from CTE</a> so I added the update statement to the bottom of the query</p>
  <pre><code>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
  </code></pre>
  <p>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.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Members_id</th>
  <th>Credentials</th>
  <th>CredCount</th>
  <th>NewPriceTotal</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>CMA, CPR</td>
  <td>2</td>
  <td>25</td>
  </tr>
  <tr>
  <td>2</td>
  <td>CMA, CPR</td>
  <td>2</td>
  <td>25</td>
  </tr>
  <tr>
  <td>3</td>
  <td>CMA, CPR</td>
  <td>2</td>
  <td>25</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>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.</p>