Updating table using looping cursors

Question

To assist in understanding I have a table like this:

itemcode itemname icode serialnum
1        A        10    0
2        B        10    0
3        C        10    0
4        D        11    0
5        E        13    0
6        F        20    0
7        G        20    0

I want the result to look like the table below using a single update query with the help of looping cursors:

itemcode itemname icode serialnum
1        A        10    1
2        B        10    2
3        C        10    3
4        D        11    1
5        E        13    1
6        F        20    1
7        G        20    2

Item code is the primary key in this table. The logic behind generating the serial number is whenever icode changes the serial number gets reset to 1. I need a single update query to update the table with the help of cursors if someone can assist with a solution?

Answer 1

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table Item
(
  itemcode int,
  itemname char(1),
  icode int,
  serialnum int
)

insert into Item values
(1, 'A', 10, 0),
(2, 'B', 20, 0),
(3, 'C', 11, 0),
(4, 'D', 10, 0),
(5, 'E', 20, 0),
(6, 'F', 10, 0),
(7, 'G', 13, 0)

Query 1:

update I
set serialnum = rn
from
  (
    select serialnum,
           row_number() over(partition by icode order by itemcode) as rn
    from Item
  ) I

select *
from Item

Results:

| ITEMCODE | ITEMNAME | ICODE | SERIALNUM |
-------------------------------------------
|        1 |        A |    10 |         1 |
|        2 |        B |    20 |         1 |
|        3 |        C |    11 |         1 |
|        4 |        D |    10 |         2 |
|        5 |        E |    20 |         2 |
|        6 |        F |    10 |         3 |
|        7 |        G |    13 |         1 |

Update

A version that uses a cursor instead of not using a cursor.

SQL Fiddle

declare @itemcode int
declare @rn int

declare ItemCursor cursor local static forward_only read_only for 
select itemcode,
       row_number() over(partition by icode order by itemcode) as rn
from Item

open ItemCursor

fetch next from ItemCursor
into @itemcode, @rn

while @@fetch_status = 0
begin
  update Item
  set serialnum = @rn
  where itemcode = @itemcode

  fetch next from ItemCursor
  into @itemcode, @rn
end

close ItemCursor
deallocate ItemCursor

Answer 2

So you want a sequential serial number for every icode-group. Then you can use ROW_NUMBER with PARTITION BY icode:

WITH CTE AS
(
  SELECT itemcode, itemname, icode, serialnum,
     ,   ROW_NUMBER() OVER (PARTITION BY icode ORDER BY itemcode) AS RN
  FROM dbo.TableName 
)
UPDATE CTE SET serialnum = RN;

If you want it to be reculculated on every update you could use a trigger:

CREATE TRIGGER dbo.TableName_Updated
ON dbo.TableName 
FOR UPDATE /* Fire this trigger when one or multiple rows are UPDATEd */
AS BEGIN
    WITH CTE AS
    (
      SELECT itemcode, itemname, icode, serialnum,
         ,   ROW_NUMBER() OVER (PARTITION BY icode ORDER BY itemcode) AS RN
      FROM dbo.TableName t INNER JOIN INSERTED i 
        ON t.itemcode = i.itemcode
    )
    UPDATE CTE SET serialnum = RN
END