UPDATE and DELETE Statements

Positioned UPDATE and DELETE statements are used in conjunction with browse cursors and include WHERE CURRENT OF clauses instead of search condition clauses. The WHERE CURRENT OF clause specifies the location of the corresponding cursor.

Before a cursor can be used by a positioned UPDATE or DELETE statement, the SELECT statement in the cursor declaration must contain the FOR BROWSE option. (The Microsoft® SQL Server™ 2000 FOR BROWSE option is similar to the FOR UPDATE option in other SQL databases, but you must use SQL Server syntax.) To use the FOR BROWSE option, the table must have both a unique index and a timestamp column.

When performing a positioned UPDATE or DELETE statement, a method called optimistic concurrency control helps prevent conflicts with other users. Optimistic concurrency control allows users to share data with less interference than they would experience with locking, which is the alternative concurrency control method.

Although optimistic concurrency control minimizes the likelihood of conflicts with other users, write your application so that it can handle updates to tables that are rejected due to locking conflicts or other problems. Use the SQLCODE field in the SQL communications area (SQLCA) data structure to detect conflicts with other users. (A SQLCODE value of -532 means the positioned UPDATE or DELETE statement failed because of a conflict with another user.) For more information about the SQLCODE field, see Using the SQLCA Data Structure. For more information about browse-mode processing, see Advanced Programming.

A positioned update can be performed twice on the same row. To do this, use the FETCH statement to obtain the row, begin a transaction by using BEGIN TRANSACTION, and update a nonkey column to itself. This locks the row and prevents other users from reading or updating it until a COMMIT TRANSACTION statement is issued.