Using CTE instead of Cursor

Question

I have the following table structure.

I just want to update SubId to all the rows where it is null and where the RawLineNumber is ascending by 1 and also the SeqNumber ascending by 1.

RawlineNumber Claimid SubId SeqNumber
1             6000    A100  1
2             6000    NULL  2
3             6000    NULL  3
10            6000    A200  1
11            6000    NULL  2
25            6000    A300  1
26            6000    NULL  2
27            6000    NULL  3

I want to update
SubId of RawLineNumber 2 and 3 with A100,
SubId of RawLineNumber 11 with A200,
SubId of RawLineNumber 26 and 27 with A300.

I have a cursor which does the job but can I have a CTE to take care of it ?

Answer 1

Since a recusive solution was requested, I decided to write one. Also it works for gaps in Seqnumbers and RawlineNumber

declare @t table (RawlineNumber int, Claimid int, SubId varchar(5), SeqNumber int)

insert @t values(1, 6000, 'A100', 1)
insert @t values(2, 6000, NULL, 2)
insert @t values(3, 6000, NULL, 3)
insert @t values(10, 6000, 'A200', 1)
insert @t values(11, 6000, NULL, 2)
insert @t values(25, 6000, 'A300', 1)
insert @t values(26, 6000, NULL, 2)
insert @t values(27, 6000, NULL, 3)

;with cte as
(
select Rawlinenumber, SeqNumber, SubId
from @t where SubId is not null and SeqNumber = 1
union all
select t.Rawlinenumber, t.SeqNumber, c.SubId
from cte c
join
@t t
on c.Rawlinenumber + 1 = t.Rawlinenumber
and c.SeqNumber + 1 = t.SeqNumber
where t.SubId is null and t.SeqNumber > 1
)
update t 
set SubId = c.SubId
from @t t join cte c 
on c.Rawlinenumber = t.Rawlinenumber
where t.SeqNumber > 1

select * from @t

Answer 2

A not-so simple SQL script should achieve what you want:

update my_table t1 set t1.subid = 
  (select t2.subid from my_table t2 
   where t2.rawlinenumber < t1.rawlinenumber
   and t2.seqnumber = 1
   and t2.rawlinenumber = (
                   select max(t3.rawlinenumber)
                   from my_table t3 
                   where t3.seq_number = 1
                   and t3.rawlinenumber <= t2.rawlinenumber)
where t1.subid is null;

The inner subselect (T3) gives us the last row having seqnumber = 1 before the current line, the outer subselect gives us the SubID for this row (using windowing functions would be more efficient, but since you didn't mention a specific RDBMS, I stick with this :-) )