title: Need Help Using Update statement in a CTE
body: <p>I created a CTE to create a Flag=1 for each IndividualID where the A1.InfantSum > A2.InfantSum and the query works the way it should: </p>
  
  <pre><code>WITH ATQInfant_CTE AS 
  (
      SELECT
          *,
          ROW_NUMBER() OVER (PARTITION BY ATQInfant_IndividualID ORDER BY ATQInfant_CreateDate) AS rownum
      FROM 
          [dbo].[vCDCP_rptInfantImprove]
  )
  SELECT DISTINCT 
      A1.ATQInfant_IndividualID,
      A1.ATQInfant_CreateDate,
      A1.InfantSum,
      A2.InfantSum,
      CASE 
         WHEN A1.InfantSum &gt; A2.InfantSum THEN 1 ELSE 0 
      END AS flag
  FROM
      ATQInfant_CTE AS A1
  INNER JOIN
      ATQInfant_CTE AS A2 ON A1.ATQInfant_IndividualID = A2.ATQInfant_IndividualID 
                          AND A1.rownum = A2.rownum + 1
  </code></pre>
  
  <p>I get the following result, but need to have the Flag=0 if the IndividualID has more than one '1'.</p>
  
  <p><a href="https://i.stack.imgur.com/FDYdT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FDYdT.png" alt="enter image description here"></a></p>
  
  <p>I'm not sure how to incorporate the Update part to the query so that I can set the Flag to 0 if the IndividualID has more than one '1' in the flag. When I add the Update query to the end, it does not recognize 'Flag' as  a column name in the query: Can someone let me know how I can add the Update query to the CTE?</p>
  
  <pre><code>UPDATE [dbo].[vCDCP_rptInfantImprove]
  SET flag = '0'
  WHERE flag = 1 AND a2.rownum + 1 &gt; 1
  </code></pre>
  
  <p>What the new query results look like:</p>
  
  <p><a href="https://i.stack.imgur.com/FDYdT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FDYdT.png" alt="enter image description here"></a></p>