Update rows based on previous value of same column. (MSSQL2008)

Question

I have a table with one column:

ColA    Rownumb
1   1
1   2
1   3
2   4
1   5
1   6
1   7
2   8
2   9
2   10
2   11
2   12
2   13
2   14
3   15
2   16
2   17
2   18
3   19
3   20
3   21
3   22
3   23
3   24

On row 4 the value of ColA changes for the first time. From row 8 it has changed permanently in the sense that following rows also have value 2. I want to update rows 5, 6 and 7 so that value 1 becomes 2. The same logic goes for rows 16, 17 and 18. In that case I want to update ColA from 2 to 3.

So how do I updated the rows as specified above?

Thanks.

Answer 1

Since you area using SQL-Server-2008 you can do this with recursive common table expression:

DECLARE @DataSource TABLE
(
    [ColA] INT
   ,[Rownumb] INT
);

INSERT INTO @DataSource ([ColA], [Rownumb])
VALUES (1, 1), (1, 2), (1, 3), (2, 4), (1, 5), (1, 6), (1, 7), (2, 8), (2, 9), (2, 10), (2, 11), (2, 12), (2, 13), (2, 14), (3, 15), (2, 16), (2, 17), (2, 18), (3, 19), (3, 20), (3, 21), (3, 22), (3, 23), (3, 24);


WITH DataSourceRecursive AS
(
    SELECT [ColA]
          ,[Rownumb]
    FROM @DataSource
    WHERE [Rownumb] = 1
    UNION ALL
    SELECT CASE WHEN DS1.[ColA] < DSR.[ColA] THEN DSR.[ColA] ELSE DS1.[ColA] END
          ,DS1.[Rownumb]
    FROM @DataSource DS1
    INNER JOIN DataSourceRecursive DSR
        ON DS1.[Rownumb] = DSR.[RowNumb] + 1
)
SELECT *
FROM DataSourceRecursive;