Modifies the data at the current location of a cursor.
UPDATE table-list SET set-item, … WHERE CURRENT OF cursor-name
identifier | hostvar
column-name [.field-name…] = scalar-value )
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 following is an example of an UPDATE statement WHERE CURRENT OF cursor:
UPDATE Employees SET surname = 'Jones' WHERE CURRENT OF emp_cursor
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.
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.
SQL92 Entry-level feature. The range of cursors that can be updated may contain vendor extensions if the ANSI_UPDATE_CONSTRAINTS option is set to OFF.
SQL99 Core feature. The range of cursors that can be updated may contain vendor extensions if the ANSI_UPDATE_CONSTRAINTS option is set to OFF.
Sybase Embedded SQL use is supported by Open Client/Open Server, and procedure and trigger use is supported in SQL Anywhere.
Must have UPDATE permission on the columns being modified.
DECLARE CURSOR statement [ESQL] [SP]
DELETE (positioned) statement [ESQL] [SP]
sp_iqcursorinfo procedure in Chapter 7, “System Procedures” in Reference: Building Blocks, Tables, and Procedures
