Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
hello guys, i need help Smile | :)

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 result like below table using Single Update Query 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 1

item code is the pimary key in this table.
 
Logic behind generating serial number is whenever the icode changes the serialnum gets reseted to 1 Smile | :)
 
Using Cursors i have to update the data in one shot and in one update query

Please Help me in solving this.

Thanks and regards
Harsha
Posted 13-Nov-12 21:39pm
Edited 14-Nov-12 0:01am
v3
Comments
Shanalal Kasim at 14-Nov-12 3:44am
   
This question is duplicate of http://www.codeproject.com/Questions/493238/Ipluswantplustoplusupdateplustableplususingplusloo
Harsha Dev at 14-Nov-12 4:11am
   
I have only posted again to get result of table above using looping cursor.
i need a query. :)
E.F. Nijboer at 14-Nov-12 5:19am
   
Your question is very unclear and when you also ask questions almost that are almost similar, it also looks like this might just be homework.
Harsha Dev at 14-Nov-12 5:29am
   
hmmm ok lemme update the question?
 
Question Updated Niijboer :)
bopannavb at 14-Nov-12 5:57am
   
icode is changing in second row also still the serial number is still is 1?
Harsha Dev at 14-Nov-12 6:00am
   
ok wait i will update the question for better understanding...juz 1 min
Harsha Dev at 14-Nov-12 6:04am
   
updated bopannavb :)

1 solution

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

Solution 1

Hi,
 
To generate serial number you can use below Example:
 
Create proc proc_UpdateGenSerial
As
Begin
	declare cur_GenSerial cursor 
	For select itemcode,icode from tbl_TableName
	Declare @itemcode int,@icode int,@oldicode  int,@newserialnum int
	set @newserialnum = 1
	open cur_GenSerial 
		fetch next from cur_GenSerial into  @itemcode,@icode
		while(@@FETCH_STATUS=0)
		Begin
			if @itemcode = 1
				Begin
					set	@oldicode = @icode
				End
			
			if(@oldicode = @icode)
				Begin
					Update	tbl_GenSerialNum
					Set		serialnum = @newserialnum
					Where	itemcode = @itemcode
					
					set @newserialnum = @newserialnum + 1
				End
			Else
				Begin
					set @newserialnum = 1
					
					Update	tbl_GenSerialNum
					Set		serialnum = @newserialnum
					Where	itemcode = @itemcode
					
					set @newserialnum = @newserialnum + 1
					set	@oldicode = @icode
				End
		fetch next from cur_GenSerial into  @itemcode,@icode
		End
	Close cur_GenSerial 
	Deallocate cur_GenSerial 
End
 
I think above Example should be helpful to solve your problem...
  Permalink  
v2
Comments
Harsha Dev at 14-Nov-12 9:24am
   
Thanks VedPrakash !!! :) Nice Work...its Working Perfectly :)

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 6,745
1 OriginalGriff 6,696
2 CPallini 5,315
3 George Jonsson 3,599
4 Gihan Liyanage 2,650


Advertise | Privacy | Mobile
Web01 | 2.8.140922.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