Professional SQL Server™ 2005 Programming

15.5. Altering Data within Your Cursor

Up until now, we've kind of glossed over the notion of changing data directly in the cursor. Now it's time to take a look at updating and deleting records within a cursor.

Since we're dealing with a specific row rather than set data, we need some special syntax to tell SQL Server that we want to update. Happily, this syntax is actually quite easy given that you already know how to perform an UPDATE or DELETE.

Essentially, we're going to update or delete data in the table that is underlying our cursor. Doing this is as simple as running the same UPDATE and DELETE statements that we're now used to, but qualifying them with a WHERE clause that matches our cursor row. We just add one line of syntax to our DELETE or UPDATE statement:

WHERE CURRENT OF <cursor name>

Nothing remarkable about it at all. Just for grins though, we'll go ahead and implement a cursor using this syntax:

USE AdventureWorks /* Build the table that we'll be playing with this time */ SELECT SalesOrderID, CustomerID INTO CursorTable FROM Sales.SalesOrderHeader WHERE SalesOrderID BETWEEN 43661 AND 43665 -- Now create a unique index on it in the form of a primary key ALTER TABLE CursorTable ADD CONSTRAINT PKCursor PRIMARY KEY (SalesOrderID) /* The IDENTITY property was automatically brought over when ** we did our SELECT INTO, but I want to use my own OrderID ** value, so I'm going to turn IDENTITY_INSERT on so that I ** can override the identity value. */ SET IDENTITY_INSERT ...