What was the previous value

Question

What was the previous value

cgreathouse

SSC Eights!

Points: 855
More actions
March 16, 2015 at 12:46 pm

#295960

What I'm trying to is update a row to whatever the previous value was. I'm trying to use the quirky update method. I thought I've done this before but I can't seem to remember the trick
Here's the setup script
declare @t table(Value int
,Previous int)
insert @t(Value)
values(1)
,(1)
,(2)
,(3)
,(4)
,(4)
,(4)
declare @preVal int
update @t
set Previous = @preVal
,@preVal = Value
select *
from @t
/* Trying to get it to look like this
ValuePrevious
1NULL
11
21
32
43
44
44
*/
What is the trick I'm missing?
Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101097 More actions · Answer 1

Quirky update requires a clustered index that conforms to the computation being done.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lowell SSC Guru Points: 323518 More actions · Answer 2

SQL doesn't have a concept of previous row, so you have to have a structure that supports being able to identify a row and some sort of row order explicitly.

here's one way to do it, via an idnetity column:

declare @t table(TableId int identity(1,1) not null,

Value int

,Previous int)

insert @t(Value)

values(1)

,(1)

,(2)

,(3)

,(4)

update t1

set t1.Previous = t2.Value,

t1.Value = t2.Value

from @t t1

inner join @t t2 on T1.TableId+ 1 = t2.TableId

SELECT * FROM @t

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!