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 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply