CODEWITHSUNDEEP
sqlsql-serversql-server-2008database-cursor
user717316
user717316

Reputation: 99

Update with a cursor in SQL Server 2008 R2

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.

Upvotes: 5

Views: 50984

Answers (4)

satyajit
satyajit

Reputation: 19

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

Upvotes: 1

Sunandan Dutt
Sunandan Dutt

Reputation: 67

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

Upvotes: -1

Lamak
Lamak

Reputation: 70638

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

Upvotes: 4

Quassnoi
Quassnoi

Reputation: 425341

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

Upvotes: 11

Related Questions