updating a column using cursor having loops

Question

I have a table

| code | descd | slnum |
|------|-------|-------|
| 10   | a     | 0     |
| 10   | b     | 0     |
| 12   | c     | 0     |
| 12   | d     | 0     |
| 11   | e     | 0     |
| 11   | f     | 0     |

And I have to update slnum column like this using cursor having loops

| code | descd | slnum |
|------|-------|-------|
| 10   | a     | 1     |
| 10   | b     | 2     |
| 12   | c     | 1     |
| 12   | d     | 2     |
| 11   | e     | 1     |
| 12   | f     | 3     |

How to resolve this? I have tried like this but its not giving me correct output

DECLARE @value INT  
DECLARE @s INT=1   

DECLARE scursor  CURSOR FOR  

SELECT slnum  
FROM trec   
for update of slnum  

OPEN scursor  

FETCH NEXT FROM scursor  
INTO @value  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    if exists(select * from trec) -- missing  
    begin  
    update trec  
    set slnum=@s  
    where current of scursor  
    select @s=@s+1  
    end  
    else   
    begin  
    update trec  
    set slnum=@s          
    where current of scursor  
    end  
    FETCH NEXT FROM scursor INTO @value  
END  

CLOSE scursor  
DEALLOCATE scursor

must you use cursor to do that ? There are easier solution without needing cursor — Squirrel
– Squirrel, Commented Aug 29, 2016 at 8:52
what is the reason for last two rows slno ?,i mean they having value of 1 and 3 — TheGameiswar
– TheGameiswar, Commented Aug 29, 2016 at 8:56
the. cursor query should include an ORDER BY code, descd clause — Squirrel
– Squirrel, Commented Aug 29, 2016 at 9:03

Tim Biegeleisen · Accepted Answer · 2016-08-29 09:02:38Z

2

I don't know whether or not you must use a cursor, but your query is a dead ringer for an UPDATE with join, using ROW_NUMBER:

UPDATE t1
SET
    slnum = t2.slnum
FROM
    yourTable t1
INNER JOIN
(
    SELECT code,
           descd,
           ROW_NUMBER() OVER(PARTITION BY code ORDER BY descd) AS slnum
    FROM yourTable
) t2
    ON t1.code = t2.code AND
       t1.descd = t2.descd

edited Aug 29, 2016 at 9:02

answered Aug 29, 2016 at 8:58

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sh_mispl Over a year ago

i know these things to update but using a cursor is homework for me

Sh_mispl · Accepted Answer · 2016-08-31 04:26:33Z

0

DECLARE @descd varchar(3)  
DECLARE @s int   

DECLARE scursor cursor local static forward_only read_only for   
SELECT descd,  
       row_number() over(partition by code order by descd) as s  
FROM trec  

OPEN scursor  

FETCH NEXT FROM scursor  
INTO @descd, @s  

WHILE @@fetch_status = 0  
BEGIN  
    update trec  
    set slnum = @s  
    where descd = @descd  

  FETCH NEXT FROM scursor  
  INTO @descd, @s  
END  

CLOSE scursor  
DEALLOCATE scursor

answered Aug 31, 2016 at 4:26

Sh_mispl

351 silver badge5 bronze badges

Comments

Sh_mispl · Accepted Answer · 2016-09-01 10:33:24Z

0

DECLARE @descd varchar(3)  
DECLARE @codeOld int   
DECLARE @code int  
DECLARE @slnum int = 1                     

DECLARE cur2 CURSOR  
  FOR  
    SELECT descd,code  
    FROM trec      
    ORDER BY code,descd          
  OPEN cur2  
  FETCH NEXT FROM cur2 INTO @descd, @code  
  WHILE @@FETCH_STATUS = 0  
    BEGIN    

       if @codeold = @code
      begin
        set @slnum = @slnum+1
      end
      else
      begin
        set @slnum = 1  
      end

      UPDATE trec
      SET slnum = @slnum                  
      WHERE descd = @descd              
      SET @codeOld = @code                   

      FETCH NEXT FROM cur2 INTO @descd, @code 
    END

  CLOSE cur2
  DEALLOCATE cur2

answered Sep 1, 2016 at 10:33

Sh_mispl

351 silver badge5 bronze badges

Comments

Sh_mispl · Accepted Answer · 2016-09-01 10:41:19Z

0

DECLARE @descd varchar(3)
DECLARE @codeOld int 
DECLARE @code int
DECLARE @slnum int = 1                   

DECLARE cur2 CURSOR
  FOR
    SELECT descd,code
    FROM trec    
    ORDER BY code,descd        
  OPEN cur2
  FETCH NEXT FROM cur2 INTO @descd, @code
  WHILE @@FETCH_STATUS = 0
    BEGIN  

      SELECT @slnum = CASE 
      WHEN @codeOld = @code 
      THEN @slnum + 1 
      ELSE 1 
      END

      UPDATE trec
      SET slnum = @slnum                  
      WHERE descd = @descd              
      SET @codeOld = @code  

      FETCH NEXT FROM cur2 INTO @descd, @code      
    END

  CLOSE cur2
  DEALLOCATE cur2

answered Sep 1, 2016 at 10:41

Sh_mispl

351 silver badge5 bronze badges

Collectives™ on Stack Overflow

updating a column using cursor having loops

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related