March 16, 2015 at 12:46 pm
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!
March 16, 2015 at 1:00 pm
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".
March 16, 2015 at 1:02 pm
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)
,(4)
,(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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply