Click here to Skip to main content
13,045,984 members (48,213 online)
Rate this:
Please Sign up or sign in to vote.
See more:
hello guys, i need help :)

i have a table like this

itemcode itemname icode serialnum
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

i want result like below table using Single Update Query Looping Cursors

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

item code is the pimary key in this table.

Please Help me in solving this asap as i am new to Curosrs

Thanks and regards
Posted 13-Nov-12 16:54pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Refer :[^]

Simple example(This sample taken from above reference)

Selecting Account Id Using CURSOR

DECLARE @AccountID INT  -- Declaring a variable for holding Account id
DECLARE @getAccountID CURSOR -- Declaring Cureser
--Initializing Cursor 
SET @getAccountID = CURSOR FOR 
FROM Accounts
--Opening Cursor
OPEN @getAccountID
--Fetching First Value
--Assigning selected value into Variable
FROM @getAccountID INTO @AccountID
--Checking Fetching Status(@@FETCH_STATUS this return the last fetch status
--  0  - The FETCH statement was successful.
-- -1 - The FETCH statement failed or the row was beyond the result set.
-- -2 - The row fetched is missing.)and starting while loop
PRINT @AccountID -- Printing AccountId. Here you can write your code.
--Fetching Next value
FROM @getAccountID INTO @AccountID
--Closing Cursor
CLOSE @getAccountID
--Releasing Cursor


update Table1  set serialnum = b.row1 from  Table1 as a inner JOIN
 (select itemcode,ROW_NUMBER() OVER(PARTITION BY icode  ORDER BY icode DESC) as row1   from Table1) as b
 on a.itemcode = b.itemcode
Harsha Dev 14-Nov-12 3:28am
ya it is correct kasim but i need in terms of looping Cursor !!! :)
Shanalal Kasim 14-Nov-12 3:36am
Harsha Dev 14-Nov-12 4:10am
I have only posted again to get result of table above using looping cursor.
i need a query. :)
Shanalal Kasim 14-Nov-12 4:19am
Provide more details for 'serialnum'.
The first 3 records serialnum is 1, 4&5 record serialnum is 2 please explain how to calculate this serialnum?
Harsha Dev 14-Nov-12 4:21am
i want to update this serial number with the help of itemicode and itemcode columns!!!
it should update in one shot using loop cursor :)

Shanalal Kasim 14-Nov-12 4:25am
Then you can use single update statement.
Can you explain the serial number generating method?
Harsha Dev 14-Nov-12 4:32am
ok right now u can take like this...
when the item code is 10 it will generate serial number 1,2,3 to the respective itemname and itemcode
Shanalal Kasim 14-Nov-12 5:09am
Please check my solution
Harsha Dev 14-Nov-12 5:09am
when the item code changes serial num gets reseted to 1
Shanalal Kasim 14-Nov-12 5:11am
Try my new solution
Harsha Dev 14-Nov-12 5:10am
logic behind calculation of Serial_Num is when the Item_Icode changes the serial num gets reseted to 1
Shanalal Kasim 14-Nov-12 5:12am
Try my new solution, tell me its working or not?
Harsha Dev 14-Nov-12 5:18am
Yeah Thanks man!!! :) Nope :( i am getting serial num as 3212111 instead of 1112231
Shanalal Kasim 14-Nov-12 5:23am
Change ->BY icode DESC to BY icode ASC
Harsha Dev 14-Nov-12 5:26am
hmmm its working Thanks man :) but i need with the help of looping Cursors.
Shanalal Kasim 14-Nov-12 5:28am
Shanalal Kasim 14-Nov-12 5:29am
This solution was helped for solving your issue, then mark it as answer.
Shanalal Kasim 14-Nov-12 5:29am
Give rating
Harsha Dev 14-Nov-12 5:42am
yeah sure :)
Shanalal Kasim 14-Nov-12 5:43am
Do you check this link for looping Cursors
Harsha Dev 14-Nov-12 5:51am
ya i am going through..Thanks Man :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 14 Nov 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100