使用循环游标更新表 [英] Using Looping Cursors Update a Table

问题描述

大家好，我需要帮助:)

我有这样的桌子

itemcode itemname icode序列号
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

我想要使​​用单更新查询循环游标"的结果如下表所示

itemcode itemname icode序列号
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

项目代码是此表中的主要关键字.

生成序列号的逻辑是，只要icode更改序列号，就将其重置为1 :)

使用游标，我必须在一枪和一次更新查询中更新数据

请帮助我解决这个问题.

谢谢和问候
Harsha

hello guys, i need help :)

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 :)

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

推荐答案

要生成序列号，您可以使用以下示例:

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...

这篇关于使用循环游标更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！