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 ?

Quassnoi · Accepted Answer · 2011-07-18T14:57:03.327

7

UPDATE  m
SET     subid = q.subid 
FROM    mytable m
CROSS APPLY
        (
        SELECT  TOP 1 subid 
        FROM    mytable mi
        WHERE   mi.rawLineNumber  < m.rawLineNumber 
                AND mi.subid IS NOT NULL
        ORDER BY
                rawLineNumber DESC
        ) q
WHERE   m.subid IS NULL

edited Jul 18 '11 at 14:57

answered Jul 18 '11 at 14:30

Quassnoi

413,100
91
616
614

i am just new to be `CROSS APPLY` .Could you pls let me know what field you are using as `id` here. – Ashley John Jul 18 '11 at 14:51
1

I think he means RawlineNumber – Aducci Jul 18 '11 at 14:54
@Quassnoi. Thanks a lot . it does the job. Meanwhile i have learn what CROSS APPLY Does.. :-) – Ashley John Jul 18 '11 at 15:08
I just tested this, it doesnt work for gabs in RawlineNumber and doesn't work for broken SeqNumber (that is 1,2,4) – t-clausen.dk Jul 18 '11 at 17:24
@tclausen: the query does not reference `seqnumber` at all. – Quassnoi Jul 18 '11 at 20:29
2

Quassnoi - no, it sure doesn't. It looks nice, but it is very simplified compared to the description of the question. I do believe people have been very generous with points. – t-clausen.dk Jul 18 '11 at 20:46

t-clausen.dk · Answer 2 · 2011-07-18T20:47:29.727

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

Thanks for this.In my scenario there wont be gaps in seq and RawLineNumber.so didn't test for those scenarios...so Quassnoi's solution works well. — Ashley John, Jul 19 '11 at 05:35

Frank Schmitt · Answer 3 · 2011-07-18T14:35:50.430

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 :-) )

Using CTE instead of Cursor

3 Answers3