我想使用循环游标更新表 [英] 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

推荐答案

参考:

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屋！