O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 -- ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required