x

HOW to Update column from previous row

Hello, I have a table of incidents with a level, an ID and an updateTime

incidentID   Level     UpdateTime
--------------------------------------
 INC001       9         2011/01/01 20:50
 INC001       NULL      2011/01/01 21:00
 INC001       24        2011/01/01 21:13
 INC001       NULL      2011/01/01 21:15
 INC001       NULL      2011/01/01 21:19
 INC001       11        2011/01/01 21:30
 INC001       11        2011/01/01 21:30
 INC002       12        2011/01/01 13:30
 INC002       NULL      2011/01/01 13:50
 INC002       2         2011/01/01 13:55

The goal is to replace the NULL values ​​of the column ‘Level’ by the previous row’s ‘Level ‘( for each ID) :

incidentID   Level     UpdateTime
--------------------------------------
 INC001        9        2011/01/01 20:50
 INC001        9        2011/01/01 21:00
 INC001        24       2011/01/01 21:13
 INC001        24       2011/01/01 21:15
 INC001        24       2011/01/01 21:19
 INC001        11       2011/01/01 21:30
 INC001        11       2011/01/01 21:30
 INC002        12       2011/01/01 13:30
 INC002        12       2011/01/01 13:50
 INC002        2        2011/01/01 13:55

Any suggestion??

thx

more ▼

asked Jul 11, 2011 at 12:24 PM in Default

depp gravatar image

depp
1 1 1 1

@depp Does your table have a primary key enforced by a clustered index? For example, IncidentID and UpdateTime combination could be a choice but it is not possible to cluster the table like this based on your sample data because you have 2 identical records in there (ID = INC001, Level = 9 and UpdateTime = 2011/01/01 21:30) for 2 records. With data like you have, how do you want the engine to figure out which record is "previous" and which is "next". This is crucial for creating a solution to your question.
Jul 11, 2011 at 01:12 PM Oleg
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Assuming that your table is clustered in such a way that the data ordered by the clustered column(s) actually comes out in the order matching what you perceive as previous and next, you can try to use the quirky update to update your records:

declare @level int;
declare @prevID char(6);

update YourTable
set 
    @level = [Level] = 
        case 
            when [Level] is null and @prevID = incidentID then @level 
            else [Level] 
        end,
    @prevID = incidentID
option (maxdop 1);

For details, please refer to the excellent article by Jeff Moden titled Solving the Running Total and Ordinal Rank Problems. Robyn Page's [SQL Server Cursor Workbench][2] is also a must read on the subject. She is the one responsible for creating the quirky update term after all.

Oleg

[2]: http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/
more ▼

answered Jul 11, 2011 at 02:05 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

That thing blows my mind every time... every time...
Jul 11, 2011 at 02:32 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

Oleg - you rock! :)

www.1klik.com
more ▼

answered Mar 13 at 01:01 PM

deSiGN gravatar image

deSiGN
1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x292

asked: Jul 11, 2011 at 12:24 PM

Seen: 1646 times

Last Updated: Mar 13 at 01:02 PM