UPDATE (positioned) statement [ESQL] [SP]

Description

Modifies the data at the current location of a cursor.

Syntax

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

Parameters

cursor-name:

identifier | hostvar

set-item:

column-name [.field-name…] = scalar-value )

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.

Examples

Example 1

The following is an example of an UPDATE statement WHERE CURRENT OF cursor:

UPDATE employee SET emp_lname = 'Jones'
WHERE CURRENT OF emp_cursor;

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.

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.

Standards

Permissions

Must have UPDATE permission on the columns being modified.

See also

DECLARE CURSOR statement [ESQL] [SP]

DELETE statement

DELETE (positioned) statement [ESQL] [SP]

UPDATE statement

“sp_iqcursorinfo procedure”