Guru's Guide to SQL Server Architecture and Internals, The

Updating Cursors

The WHERE CURRENT OF clause of the UPDATE and DELETE commands allows you to update and delete rows via a cursor. An update or delete performed via a cursor is known as a positioned update or delete. Listing 14.21 shows an example.

Listing 14.21. Performing a Positioned Update and Delete

USE pubs SET CURSOR_CLOSE_ON_COMMIT OFF SET NOCOUNT ON DECLARE C CURSOR DYNAMIC FOR SELECT * FROM sales OPEN c FETCH c BEGIN TRAN -- Start a transaction so we can reverse our changes -- A positioned UPDATE UPDATE sales SET qty=qty+1 WHERE CURRENT OF c FETCH RELATIVE 0 FROM c FETCH c -- A positioned DELETE DELETE sales WHERE CURRENT OF c SELECT * FROM sales WHERE qty=3 ROLLBACK TRAN -- Throw away our changes SELECT * FROM sales WHERE qty=3 -- ...