CODEWITHSUNDEEP
sql-server
Sh_mispl
Sh_mispl

Reputation: 35

updating a column using cursor having loops

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

Upvotes: 2

Views: 71

Answers (4)

Sh_mispl
Sh_mispl

Reputation: 35

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

Upvotes: 0

Sh_mispl
Sh_mispl

Reputation: 35

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

Upvotes: 0

Sh_mispl
Sh_mispl

Reputation: 35

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

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

Upvotes: 2

Related Questions