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

avatar 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 is also a must read on the subject. She is the one responsible for creating the quirky update term after all.

Oleg

more ▼

answered Jul 11, 2011 at 02:05 PM

avatar image

Oleg
17.2k 3 7 28

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, 2014 at 01:01 PM

avatar 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.

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:

x393

asked: Jul 11, 2011 at 12:24 PM

Seen: 6368 times

Last Updated: Mar 13, 2014 at 01:02 PM

Copyright 2016 Redgate Software. Privacy Policy