Binaryworld - Script - Update Blank Values of Group until Next group starts ... [ SQL Server -> T-SQL ]

Recently I came across simple scenario where I had to update some blank values of certain records. Here is what i mean

Assume you have two columns. You want to update Order# column with same order number until next group starts (e.g. ID 2,3 update with Order# => 1000).

ID Order#
1 1000
2 NULL
3 NULL
4 2000
5 NULL
6 NULL
7 3000
8 NULL
9 NULL

Click here to copy the following block

set nocount on
go

create table #tmp (ID int, OrderNum varchar(10))
insert into #tmp values(1,1000)
insert into #tmp values(2,NULL)
insert into #tmp values(3,NULL)
insert into #tmp values(4,2000)
insert into #tmp values(5,NULL)
insert into #tmp values(6,NULL)
insert into #tmp values(7,3000)
insert into #tmp values(8,NULL)
insert into #tmp values(9,NULL)

declare @id int
declare @ordernum varchar(100)
declare @prev_ordernum varchar(100)

select * from #tmp

declare c cursor
for
select id,OrderNum from #tmp

open c

fetch next from c into @id,@ordernum

set @prev_ordernum=@ordernum

while @@fetch_status<>-1
begin
--Update records with blank order#
if IsNull(@ordernum,'')='' and isnull(@prev_ordernum,'')<>''
begin
Update #tmp set ordernum=@prev_ordernum where ID=@id
Print 'updated --> ' + cast(@id as varchar(100))
end

fetch next from c into @id,@ordernum

--//New order num group found
if isnull(@ordernum,'')<>'' and @prev_ordernum<>@ordernum
set @prev_ordernum=@ordernum
end
close c
deallocate c

select * from #tmp

go

go
drop table #tmp

Output

Click here to copy the following block

ID OrderNum
1 1000
2 1000
3 1000
4 2000
5 2000
6 2000
7 3000
8 3000
9 3000