Reputation: 135
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.
Upvotes: 2
Views: 52
Reputation: 43676
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;
Upvotes: 2