Can a CTE be used to update in passes?

Question

I inherited a table with the following structure:

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

The first recs (rn=1) need to be inserted, while the rest (rn <>1) need to update the insertions (sequentially). I can insert easily, using where rn = 1 and checking for absence of the userID.

My problem is that I need to now update all recs sequentially using rn <>1 so that the user table reflects the latest state. That is, the user table after UPDATEs should look like this:

rowID  userID  Data1  Data2  Data3
-----  ------  -----  -----  -----
1      1       B      111    123
2      2       E      111    333
3      3       F      333    222

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.

Is this possible (or should I use do-while)? if so, do I need a recursive one or a "regular" CTE?

Here is what I tried:

;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<>1
    ) b
ON  b.user_id = c.user_id
WHERE 
    EXISTS
        (   Select userID 
            from userTable q
            Where q.userId = b.userId
        )

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"?

Answer 1

Following CTE returns your given output for your given inputs. You can use these results as a starting point for inserting the records into another table.

The gist of it is

• Use a recursive CTE, starting with all rows where rn=1.
• In the recursive part, pick Data1-3 from the recursive part if available, otherwise retain the exisiting value (COALESCE). The result of the CTE now is your final values + the initial values where rn=1
• Add a ROW_NUMBER for each userID but ORDER DESC on the existing rn. This makes sure that the latest values get rownumber 1.
• Finally select all with rownumber 1 and add another rownumber as per your final results.

SQL Statement

;WITH q AS (
  SELECT  rn
          , UserID
          , Data1
          , Data2
          , Data3
  FROM    Inherited
  WHERE   rn = 1
  UNION ALL
  SELECT  i.rn
          , i.UserID
          , COALESCE(i.Data1, q.Data1)
          , COALESCE(i.Data2, q.Data2)
          , COALESCE(i.Data3, q.Data3)
  FROM    q
          INNER JOIN Inherited i ON i.rn = q.rn+1 AND i.userID = q.userID
 )
 SELECT rn = ROW_NUMBER() OVER (ORDER BY userID)
        , *
 FROM   (
           SELECT UserID
                  , Data1
                  , Data2
                  , Data3 
                  , rn = ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY rn DESC)
           FROM   q

        ) t
WHERE   rn = 1                          

Test Script

;WITH Inherited (rowID, rn, userID, Data1, Data2, Data3) AS (
  SELECT * FROM (VALUES 
    (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')
  ) a (b, c, d, e, f, g)
)
, q AS (
  SELECT  rn
          , UserID
          , Data1
          , Data2
          , Data3
  FROM    Inherited
  WHERE   rn = 1
  UNION ALL
  SELECT  i.rn
          , i.UserID
          , COALESCE(i.Data1, q.Data1)
          , COALESCE(i.Data2, q.Data2)
          , COALESCE(i.Data3, q.Data3)
  FROM    q
          INNER JOIN Inherited i ON i.rn = q.rn+1 AND i.userID = q.userID
 )
 SELECT rn = ROW_NUMBER() OVER (ORDER BY userID)
        , *
 FROM   (
           SELECT UserID
                  , Data1
                  , Data2
                  , Data3 
                  , rn = ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY rn DESC)
           FROM   q

        ) t
WHERE   rn = 1