使用SQL Server 2008 R2中的游标进行更新

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(值是int类型)

The values I am trying to put in the column FK_Profiel are 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.

我想从select查询中分配所有值。

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.

请帮助,thx。

4 个解决方案

#1

You forgot to add FETCH NEXT into 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

#2

First af all, you don't need a CURSOR for this, you can do an UPDATE without it. And you also should use explicit JOINS instead of implicit ones. Try the following:

首先,你不需要CURSOR,你可以在没有它的情况下进行更新。而且你也应该使用显式JOINS而不是隐式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

#3

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

#4

-1

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

#1