question

depp avatar image
depp asked

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
tsql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@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.
2 Likes 2 ·
Oleg avatar image
Oleg answered
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][1]. 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 [1]: http://www.sqlservercentral.com/articles/T-SQL/68467/ [2]: http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
That thing blows my mind every time... every time...
0 Likes 0 ·
deSiGN avatar image
deSiGN answered
Oleg - you rock! :) www.1klik.com
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.