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.

1_simple cursor in sql server to update rows

 

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *