t-sqlsql-server-2005common-table-expressiondatabase-cursorcross-apply

Using CTE instead of Cursor


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 ?


Solution

  • 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