Update with a cursor in SQL Server 2008 R2

Question

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.

Solution 1

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

Solution 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:

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

Solution 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

Comments

[TomTom]

+1. Usage ofa cursor for that is asking the server to be slow and demosntrates a lack of set oriented thinking.