title: Can a CTE be used to update in passes?
body: <p>I inherited a table with the following structure:</p>
  
  <pre><code>rowID  rn   userID  Data1  Data2  Data3
  -----  --   ------  -----  ----   ----
  1      1    1       A      null   123
  2      2    1       B      111    null
  3      1    2       C      222    333
  4      2    2       D      null   null
  5      3    2       E      111    null
  6      1    3       F      333    222
  </code></pre>
  
  <p>The first recs (<code>rn=1</code>) need to be inserted, while the rest (<code>rn &lt;&gt;1</code>) need to update the insertions (sequentially). I can insert easily, using <code>where rn = 1</code> and checking for absence of the userID.</p>
  
  <p>My problem is that I need to now update all recs sequentially using <code>rn &lt;&gt;1</code> so that the user table reflects the latest state. That is, the user table after UPDATEs should look like this:</p>
  
  <pre><code>rowID  userID  Data1  Data2  Data3
  -----  ------  -----  -----  -----
  1      1       B      111    123
  2      2       E      111    333
  3      3       F      333    222
  </code></pre>
  
  <p>My thought was to write a CTE, where each "pass" would grab all the recs where rn=2, then rn=3, then rn=4.... until I have no more rn to process.  This way, I can update in sets.</p>
  
  <p>Is this possible (or should I use do-while)? if so, do I need a recursive one or a "regular" CTE?  </p>
  
  <p>Here is what I tried:</p>
  
  <pre><code>;with my_cte (rowID, rn, userID, Data1, Data2,  Data3, val) As 
  (
      SELECT rowID, rn, userID, Data1, Data2,  Data3, val
      from @MyTempTable x
      where rn =1
  
          UNION ALL
  
      SELECT rowID, rn, userID, Data1, Data2,  Data3, b.val +1
      from @MyTempTable y
      INNER JOIN
          my_cte b
      ON  y.userID = b.userID
      WHERE y.rn = b.val +1 
  
  )
  UPDATE  userTable
  SET
      [Data1] = COALESCE(c.Data1, [Data1])
      ,[Data2]= COALESCE(c.Data2, [Data2])
      ,[Data3]= COALESCE(c.Data3, [Data3])
  From @MyTempTable c
  JOIN
      (   SELECT user_id
          FROM my_cte
          WHERE rn&lt;&gt;1
      ) b
  ON  b.user_id = c.user_id
  WHERE 
      EXISTS
          (   Select userID 
              from userTable q
              Where q.userId = b.userId
          )
  </code></pre>
  
  <p>I could not get this to work, and it looks like only the first row is updating.  Any thoughts?  I'm a noob with CTEs. More than anything I'd like to know what exactly the CTE is doing... is it even possible for the update to run in "passes"?</p>