SQL – How to Update Records in a Table Using a Loop and Cursor

This article is a popular re-post from my Blogger blog from November of 2011 but is still relevant today.

I had a challenge yesterday that I would normally solve using some Visual Basic code but had to do it in pure SQL on a SQL 2005 server.

THE CHALLENGE:
Loop through all the records in a table and then update the table based on some logic or condition.

THE SOLUTION:

Using a SQL Cursor, I was able to loop through all the records in a table and then run an SQL update command for certain records that matched a particular criteria.

Here is the code:

DECLARE @myEmpID int
DECLARE MyCursor CURSOR FOR
SELECT DISTINCT  EmployeeID FROM Employees WHERE Company=64

OPEN myCursor
--Read the initial emploee id value from the cursor
FETCH NEXT FROM myCursor
INTO @myEmpID

WHILE @@FETCH_STATUS = 0
BEGIN

--Update goes here 
UPDATE Employees 
SET [Status] = 'T'
WHERE EmployeeID = @myEmpID AND Company=54
FETCH NEXT FROM MyCursor
INTO @myEmpID
END
CLOSE MyCursor

Hope this helps someone!

Regards,
Cyber Abyss