我想使用循环游标更新表 [英] I want to update table using looping cursors
问题描述
大家好,我需要帮助:)
我有这样的桌子
itemcode itemname icode序列号
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
我想要使用单更新查询循环游标"的结果如下表所示
itemcode itemname icode序列号
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
项目代码是此表中的主要关键字.
请帮助我尽快解决此问题,因为我是Curosrs的新手
谢谢和问候
Harsha
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
Harsha
推荐答案
参考: ^ ]
简单示例(此示例摘自上述参考文献)
使用CURSOR选择帐户ID
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
这篇关于我想使用循环游标更新表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!