UPDATE (positioned) Statement [ESQL] [SP]
Modifies the data at the current location of a cursor.
UPDATE <table-list> SET set-item, ... WHERE CURRENT OF <cursor-name> set-item <column-name> [.<field-name>…] = <scalar-value>
- identifier or hostvar
- 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.
- expression cannot contain functions or expressions.
- use of ORDER BY in the WHERE CURRENT OF clause is not recommended. 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.
- an UPDATE statement
using 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.
Since SAP IQ does not support the CREATE VIEW... WITH CHECK OPTION, positioned UPDATE does not support this option. The WITH CHECK OPTION clause 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.
SAP IQ supports repeatedly updating the same row in the result set.
- 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 SAP SQL Anywhere.