Updating a Row with Value from Preceding Row

I found this problem on one of the SQL Server forums that I am regular contributor of.

Given this table:

set nocount on
declare @yourtable table
(recnum int,
Col2 varchar(2),
FKCol3 varchar(15),
Col4 varchar(10))

insert into @yourtable values (1, 'AA', 'key0AA', 'Somedata')
insert into @yourtable values (2, 'CC', NULL, 'Somedata')
insert into @yourtable values (3, 'CC', NULL, 'Somedata')
insert into @yourtable values (4, 'AA', 'key0BB', 'Somedata')
insert into @yourtable values (5, 'BB', NULL, 'Somedata')
insert into @yourtable values (6, 'CC', NULL, 'Somedata')
insert into @yourtable values (7, 'AA', 'key0CC', 'Somedata')
insert into @yourtable values (8, 'CC', NULL, 'Somedata')
insert into @yourtable values (9, 'CC', NULL, 'Somedata')
insert into @yourtable values (10, 'AA', 'key0DD', 'Somedata')
insert into @yourtable values (11, 'AA', 'key0EE', 'Somedata')

select *
from @YourTable 


recnum      Col2 FKCol3          Col4
----------- ---- --------------- ----------
1           AA   key0AA          Somedata
2           CC   NULL            Somedata
3           CC   NULL            Somedata
4           AA   key0BB          Somedata
5           BB   NULL            Somedata
6           CC   NULL            Somedata
7           AA   key0CC          Somedata
8           CC   NULL            Somedata
9           CC   NULL            Somedata
10          AA   key0DD          Somedata
11          AA   key0EE          Somedata

The problem: The user wants to replace those PKCol3 that are NULL by the non-null PKCol3 that precedes it. In this case, records 2 and 3 will have PKCol3 with "key0AA". Records 5 and 6 will have PKCol3 = "key0BB", and so on.This has to be done for almost 2 million+ rows.

Here's the code I suggested:

First, here's a query that can be used for analysis:

select recnum, Col2, FKCol3, Col4,
newCol3 = isnull((
   select top 1 FKCol3 
   from @YourTable b    
   where a.recnum > b.recnum and b.FKCol3 is not null
    and a.FKCol3 is null 
   order by b.recnum desc),a.FKCol3)
from @YourTable a

Here's the result set:

recnum      Col2 FKCol3          Col4       newCol3
----------- ---- --------------- ---------- ---------------
1           AA   key0AA          Somedata   key0AA
2           CC   NULL            Somedata   key0AA
3           CC   NULL            Somedata   key0AA
4           AA   key0BB          Somedata   key0BB
5           BB   NULL            Somedata   key0BB
6           CC   NULL            Somedata   key0BB
7           AA   key0CC          Somedata   key0CC
8           CC   NULL            Somedata   key0CC
9           CC   NULL            Somedata   key0CC
10          AA   key0DD          Somedata   key0DD
11          AA   key0EE          Somedata   key0EE

The intention is that newCol3 will be the new value of PKCol3.

Executing the update:

update @yourtable
set FKCol3 = isnull((
   select top 1 FKCol3
   from @YourTable b 
   where a.recnum > b.recnum and b.FKCol3 is not null
   and a.FKCol3 is null 
    order by b.recnum desc),a.FKCol3)
from @yourtable a

select *
from @YourTable 

Here is the updated result set:

recnum      Col2 FKCol3          Col4
----------- ---- --------------- ----------
1           AA   key0AA          Somedata
2           CC   key0AA          Somedata
3           CC   key0AA          Somedata
4           AA   key0BB          Somedata
5           BB   key0BB          Somedata
6           CC   key0BB          Somedata
7           AA   key0CC          Somedata
8           CC   key0CC          Somedata
9           CC   key0CC          Somedata
10          AA   key0DD          Somedata
11          AA   key0EE          Somedata

This script will only work if there's a unique column (in this case recnum) that can be used to identify and sort the rows.



~~ CK