UPDATE (positioned) Statement [ESQL] [SP]

Modifies the data at the current location of a cursor.

Syntax

UPDATE table-list 
SET set-item, … 
WHERE CURRENT OF cursor-name

Parameters

Examples

Usage

This form of the UPDATE statement updates the current row of the specified cursor. The current row is defined to be the last row successfully fetched from the cursor, and the last operation on the cursor cannot have been a positioned DELETE statement.

SET—The columns that are referenced in set-item must be in the base table that is updated. They cannot refer to aliases, nor to columns from other tables or views. If the table you are updating is given a correlation name in the cursor specification, you must use the correlation name in the SET clause. The expression on the right side of the SET clause may reference columns, constants, variables, and expressions from the SELECT clause of the query. The set-item expression cannot contain functions or expressions.

The requested columns are set to the specified values for the row at the current row of the specified query. The columns must be in the select list of the specified open cursor.

Changes effected by positioned UPDATE statements are visible in the cursor result set, except where client-side caching prevents seeing these changes. Rows that are updated so that they no longer meet the requirements of the WHERE clause of the open cursor are still visible.

Sybase does not recommend the use of ORDER BY in the WHERE CURRENT OF clause. The ORDER BY columns may be updated, but the result set does not reorder. The results appear to fetch out of order and appear to be incorrect.

Since Sybase IQ does not support the CREATE VIEW... WITH CHECK OPTION, positioned UPDATE does not support this option. The WITH CHECK OPTION does not allow an update that creates a row that is not visible by the view.

A rowid column cannot be updated by a positioned UPDATE.

Sybase IQ supports repeatedly updating the same row in the result set.

See also Reference: Building Blocks, Tables, and Procedures > System Procedures > System Stored Procedures > sp_iqcursorinfo Procedure.

Standards

  • The range of cursors that can be updated may contain vendor extensions to ISO/ANSI SQL grammar if the ANSI_UPDATE_CONSTRAINTS option is set to OFF.
  • Embedded SQL use is supported by Open Client/Open Server, and procedure and trigger use is supported in SQL Anywhere.

Permissions

Must have UPDATE permission on the columns being modified.

Related reference
DECLARE CURSOR Statement [ESQL] [SP]
DELETE Statement
DELETE (positioned) Statement [ESQL] [SP]
UPDATE Statement