Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ms-sql-server
hello guys, i need help Smile | :)
 
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
Harsha
Posted 13-Nov-12 16:54pm

1 solution

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

Solution 1

Refer : http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/[^]
 
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 
SELECT Account_ID
FROM Accounts
--Opening Cursor
OPEN @getAccountID
--Fetching First Value
FETCH NEXT
--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
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID -- Printing AccountId. Here you can write your code.
--Fetching Next value
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
--Closing Cursor
CLOSE @getAccountID
--Releasing Cursor
DEALLOCATE @getAccountID
 

Try:
 
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
  Permalink  
v3
Comments
Harsha Dev at 14-Nov-12 3:28am
   
ya it is correct kasim but i need in terms of looping Cursor !!! :)
Shanalal Kasim at 14-Nov-12 3:36am
   
Refer: http://forums.devarticles.com/general-sql-development-47/for-loop-cursor-151552.html
Harsha Dev at 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 at 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 at 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 :)
 
Regards
Harsha
Shanalal Kasim at 14-Nov-12 4:25am
   
Then you can use single update statement.
Can you explain the serial number generating method?
Harsha Dev at 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 at 14-Nov-12 5:09am
   
Please check my solution
Harsha Dev at 14-Nov-12 5:09am
   
when the item code changes serial num gets reseted to 1
Shanalal Kasim at 14-Nov-12 5:11am
   
Try my new solution
Harsha Dev at 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 at 14-Nov-12 5:12am
   
Try my new solution, tell me its working or not?
Harsha Dev at 14-Nov-12 5:18am
   
Yeah Thanks man!!! :) Nope :( i am getting serial num as 3212111 instead of 1112231
Shanalal Kasim at 14-Nov-12 5:23am
   
Change ->BY icode DESC to BY icode ASC
Harsha Dev at 14-Nov-12 5:26am
   
hmmm its working Thanks man :) but i need with the help of looping Cursors.
Shanalal Kasim at 14-Nov-12 5:28am
   
Refer: http://forums.devarticles.com/general-sql-development-47/for-loop-cursor-151552.html
Shanalal Kasim at 14-Nov-12 5:29am
   
This solution was helped for solving your issue, then mark it as answer.
Shanalal Kasim at 14-Nov-12 5:29am
   
Give rating
Harsha Dev at 14-Nov-12 5:42am
   
yeah sure :)
Shanalal Kasim at 14-Nov-12 5:43am
   
Do you check this link http://forums.devarticles.com/general-sql-development-47/for-loop-cursor-151552.html for looping Cursors
Harsha Dev at 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
0 Gihan Liyanage 332
1 Sergey Alexandrovich Kryukov 230
2 ClimerChinna 222
3 vikinghunter 178
4 OriginalGriff 161
0 Sergey Alexandrovich Kryukov 8,373
1 OriginalGriff 7,112
2 CPallini 2,598
3 Richard MacCutchan 2,025
4 Abhinav S 1,788


Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 14 Nov 2012
Copyright © CodeProject, 1999-2014
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