Simple cursor in SQL server to update rows

The blog explains a simple cursor in SQL Server to update rows. This is just for explanation, the update query can be written without using cursor too. The T-SQL is given below

--simple cursor in sql server 
-- declare a cursor
DECLARE update_cursor CURSOR FOR 
SELECT SalesOrderID,OrderQty from Sales.SalesOrderDetail
WHERE SalesOrderID=43659

-- open cursor and fetch first row into variables
OPEN update_cursor
FETCH FROM update_cursor

-- check for a new row
WHILE @@FETCH_STATUS=0
BEGIN
-- do update operation
UPDATE Sales.SalesOrderDetail 
SET OrderQty=100
WHERE CURRENT OF update_cursor 
-- get next available row into variables
FETCH NEXT FROM update_cursor
END
close update_cursor
Deallocate update_cursor

The above query updates the orderqty column to 100 for a particular salesorderid. When doing a cursor update the CURRENT OF keyword is used to update the current row. If a cursor definition has a query with multiple tables then only the table used in update statement is affected.  The output of above query is shown below.

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook