Results 1 to 2 of 2

Thread: how to update

  1. #1
    Join Date
    Apr 2003
    Posts
    1

    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

  2. #2
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    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
  •