February 12, 2009 at 6:10 pm
I am trying to update the employee id in a table with data from another table using a cursor. I am not sure why it is not working. Can someone help with this? Below is the cursor.
DECLARE
@newEmpID nvarchar,
@firstname nvarchar,
@lastname nvarchar
DECLARE userUpdate CURSOR LOCAL FAST_FORWARD
FOR
Select newEmpID, firstname, lastname FROM ausersOmega
open userupdate
FETCH NEXT FROM userUpdate
INTO @firstname, @lastname, @newEmpID
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE users
set employee_id = @newEmpID
WHERE firstname = @firstname
and lastname = @lastname
FETCH NEXT FROM userUpdate INTO @firstname, @lastname, @newEmpID
END
CLOSE userupdate
DEALLOCATE userupdate
GO
These are the results:
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
I should be getting a bunch of changes, based on the following query with results nothing has changed.
select a.employee_id, au.newempid, a.firstname, a.lastname from users as a
join ausersomega as au
on a.firstname = au.firstname
and a.lastname = au.lastname
and a.status_id = 1
order by a.lastname
Results from join (last names have changed to protect the innocent 🙂
employee_id newempid firstname lastname
3043 1639 MAUREEN xxx
3788 1629 JAMEY xxxx
9794 1607 SALIMAH xxxxxx
7048 1609 KAROL xxxxx
Any help would be appreciated.
February 12, 2009 at 6:34 pm
Why are you using a cursor? it can be done with a single UPDATE statement.
UPDATE U SET employee_id = NU.newEmpID
FROM users U
JOIN ausersOmega NU ON (U.firstname = NU.firstname AND U.lastname = NU.lastname)
Another point - are there any cases of multiple users with the same forename and lastname? You had better check before executing this update .
February 12, 2009 at 6:50 pm
Thanks, I was just thinking of using a simple update statement. I am new to cursors and was trying to use a cursor as a learning thing. If you have any idea why the cursor is not working can you let me know. If not thanks for your reply.
February 12, 2009 at 6:55 pm
The order of the columns in the SELECT statement used to declare your cursor
Select newEmpID, firstname, lastname FROM ausersOmega
should match up with the order of variables in your FETCH statement:
FETCH NEXT FROM userUpdate INTO @firstname, @lastname, @newEmpID
but they don't.
February 12, 2009 at 7:19 pm
Tried the modified cursor as requested and I still get the same results. Any more ideas. I tried doing a simple update statement also but that doesn't work.
DECLARE
@newEmpID varchar,
@firstname varchar,
@lastname varchar
DECLARE userUpdate CURSOR LOCAL FAST_FORWARD
FOR
SELECT newEmpID, firstname, lastname FROM ausersOmega
OPEN userUpdate
FETCH NEXT FROM userUpdate
INTO @newEmpID, @firstname, @lastname
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE users
set employee_id = @newEmpID
WHERE firstname = @firstname
and lastname = @lastname
FETCH NEXT FROM userUpdate INTO @newEmpID, @firstname, @lastname
END
CLOSE userupdate
DEALLOCATE userupdate
GO
February 12, 2009 at 7:26 pm
Try specifying the length of the varchar local variables to match the length of the columns in your table schemas.
DECLARE
@newEmpID varchar,
@firstname varchar,
@lastname varchar
February 12, 2009 at 7:47 pm
Thank you so much for your help. The length fixed the cursor and it is working now.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply