在 SQL Server 2008 R2 中使用游标进行更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5732008/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update with a cursor in SQL Server 2008 R2
提问by user717316
I want to update a column in a specific table called Employeekopie1.
我想更新名为Employeekopie1.
The column I am trying to update is FK_Profiel(values are type int)
我要更新的列是FK_Profiel（值是 type int）
The values I am trying to put in the column FK_Profielare the values I am getting
from a cursor. The cursor is getting values from a column in a different table, using joins to get the correct values.
我试图放入列FK_Profiel中的值是我从游标中获取的值。游标正在从不同表中的列中获取值，使用连接来获取正确的值。
The result of the select query used returns multiple rows with different values.
所使用的选择查询的结果返回具有不同值的多行。
The first result of the select query is 114, which is correct. The problem is that this value is assigned to all the fields in the column FK_Profiel, which is not my intention.
select查询的第一个结果是114，这是正确的。问题是这个值被分配给了列中的所有字段FK_Profiel，这不是我的本意。
I want to assign all the values from the select query.
我想从选择查询中分配所有值。
The code is as follows:
代码如下：
DECLARE @l_profiel int;
DECLARE c1 CURSOR
FOR select p.ProfielID
from DIM_Profiel p,DIM_EmployeeKopie1 e1,employee e
where e1.EmpidOrigineel = e.emplid and e.profile_code = p.Prof_Code
for update of e1.FK_Profiel;
open c1;
FETCH NEXT FROM c1 into @l_profiel
WHILE @@FETCH_STATUS = 0
BEGIN
SET NOCOUNT ON;
UPDATE DIM_EmployeeKopie1
set FK_Profiel = @l_profiel
where current of c1
end
close c1;
deallocate c1;
Please help, thx.
请帮忙，谢谢。
回答by Quassnoi
You forgot to add FETCH NEXTinto the loop.
您忘记添加FETCH NEXT到循环中。
But you don't need a cursor for this at all.
但是您根本不需要光标。
Try this:
尝试这个：
UPDATE e1
SET FK_Profiel = p.ProfielID
FROM DIM_EmployeeKopie1 e1
JOIN employee e
ON e.emplid = e1.EmpidOrigineel
JOIN DIM_Profiel p
ON p.Prof_Code = e.profile_code
回答by Lamak
First af all, you don't need a CURSORfor this, you can do an UPDATEwithout it. And you also should use explicit JOINSinstead of implicit ones. Try the following:
首先，你不需要一个CURSOR，你可以UPDATE没有它。而且您还应该使用显式JOINS而不是隐式。请尝试以下操作：
UPDATE e1
SET FK_Profiel = p.ProfielID
FROM DIM_EmployeeKopie1 e1
JOIN employee e
ON e1.EmpidOrigineel = e.emplid
JOIN DIM_Profiel p
ON e.profile_code = p.Prof_Code
回答by satyajit
DECLARE @employee_id INT
DECLARE @getemployee_id CURSOR
SET @getemployee_id = CURSOR FOR
SELECT employee_id
FROM employment_History
OPEN @getemployee_id
FETCH NEXT FROM @getemployee_ID
INTO @employee_ID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @employee_ID
FETCH NEXT FROM @getemployee_ID
INTO @employee_id
END
CLOSE @getemployee_ID
DEALLOCATE @getemployee_ID
回答by Sunandan Dutt
This is the simplest example of the SQL Server Cursor. I have used this all the time for any use of Cursor in my T-SQL.
DECLARE @AccountID INT
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
