-
how to update
I have... (sample values)
@somevalue =3214
col1(what) col2(value) col3(valbefore)
positive 110 null
negative 232 null
positive 157 null
positive 754 null
negative 213 null
...and need to get
col1(what) col2(value) col3(valebefore)
positive 110 3324
positive 157 3481
positive 754 4235
negative 232 4003
negative 213 3790
In short, I need to update based on a previous record on a sorted table with first value acquired before
-
create proc proc12 @v1 int as
declare @flag varchar(10)
set @flag = 'positive'
declare @c1 varchar(10)
declare @c2 int
declare @c3 int
set @c3 = 0
declare cur1 cursor for select col1,col2 from mytable
open cur1
fetch next from cur1 into @c1,@c2
while @@fetch_status = 0
begin
if @flag = @c1
begin
set @c3 = @c2+@c3
update mytable set col3 = @c3+3214 where col1 = @c1 and col2 = @c2+@v1
end
ELSE
begin
set @c3 = @c3-@c2
update mytable set col3 = @c3+3214 where col1 = @c1 and col2 = @c2+@v1
end
fetch next from cur1 into @c1,@c2
end
close cur1
deallocate cur1
--Usage
--exec proc12 3214(3214 is the number --you want to execute)
--See the result
--select * from mytable order by col1 desc
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|