Day: October 2, 2019

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

This is a re-post of one of my most popular blog post from my old Blogger blog.

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.

Example Code: Update table with SQL Cursor and While Loop

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