当前位置：文江博客话题详情

在 SQL Server 2008 R2 中使用游标更新

发布于 2024-11-02 08:57:03 字数 829 浏览 1 评论 0原文

我想更新名为 Employeekopie1 的特定表中的列。

我尝试更新的列是 FK_Profiel （值的类型为 int）

我尝试放入 FK_Profiel 列中的值是这些值我正在得到从光标。游标从不同表中的列获取值，使用联接来获取正确的值。

使用的选择查询的结果返回具有不同值的多行。

select查询的第一个结果是114，这是正确的。问题是这个值被分配给FK_Profiel列中的所有字段，这不是我的本意。

我想分配选择查询中的所有值。

代码如下：

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;

请帮忙，thx。

原文

I want to update a column in a specific table called Employeekopie1.

The column I am trying to update is FK_Profiel (values are type 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.

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.

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寄风 2024-11-09 08:57:03

您忘记将 FETCH NEXT 添加到循环中。

但您根本不需要光标。

试试这个：

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

You forgot to add FETCH NEXT into the loop.

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

回复收藏 0 原文

鯉魚旗 2024-11-09 08:57:03

首先，您不需要 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

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:

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

回复收藏 0 原文

っ〆星空下的拥抱 2024-11-09 08:57:03

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

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

回复收藏 0 原文

画▽骨i 2024-11-09 08:57:03

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

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

回复收藏 0 原文

~没有更多了~