UPDATE (positioned) statement [ESQL] [SP]

Use this statement to modify the data at the current location of a cursor.

Syntax 1

UPDATE WHERE CURRENT OF cursor-name
{ USING DESCRIPTOR sqlda-name | FROM hostvar-list }

Syntax 2

UPDATE update-table, ...
SET set-item, ...
WHERE CURRENT OF cursor-name

hostvar-list : indicator variables allowed

update-table :
[owner-name.]object-name [ [ AS ] correlation-name ]

set-item :
[ correlation-name.]column-name = { expression | DEFAULT }
| [owner-name.]object-name.column-name = { expression | DEFAULT }

object-name : identifier (a table or view name)

sqlda-name : identifier

Parameters

USING DESCRIPTOR clause When assigning a variable, the variable must already be declared, and its name must begin with the "at" sign (@). Variable and column assignments can be mixed together, and any number can be used. If a name on the left side of an assignment in the SET list matches a column in the updated table and the variable name, the statement updates the column.
SET clause The columns that are referenced in set-item must be in the table or view that is updated. They cannot refer to aliases, nor to columns from other tables or views. If the table or view you are updating is given a correlation name in the cursor specification, you must use the correlation name in the SET clause.
Each set-item is associated with a single update-table, and the corresponding column of the matching table in the cursor's query is modified. The expression references columns of the tables identified in the UPDATE list and may use constants, variables, expressions from the select list of the query, or combinations of the above using operators such as +, -, ..., COALESCE, IF, and so on. The expression can not reference aliases of expressions from the cursor's query, nor can they reference columns of other tables of the cursor's query which do not appear in the UPDATE list. Subselects, subquery predicates, and aggregate functions can not be used in the set-items.

Each update-table is matched to a table in the query for the cursor as follows:
- If a correlation name is specified, it is matched to a table in the cursor's query that has the same table-or-view-name and the same correlation-name.
- Otherwise, if there is a table in the cursor's query that has the same table-or-view-name that does not have a correlation name specified, or has a correlation name that is the same as the table-or-view-name, then the update table is matched with this table in the cursor's query.
- Otherwise, if there is a single table in the cursor's query that has the same table-or-view-name as the update table, then the update table is matched with this table in the cursor's query.
If a column has a default defined, you can use the SET clause to set a column to its default value. For an example of this, see the Examples section of UPDATE statement.

Remarks

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 must not have been a positioned DELETE statement.

For syntax 1, columns from the SQLDA or values from the host variable list correspond one-to-one with the columns returned from the specified cursor. If the sqldata pointer in the SQLDA is the null pointer, the corresponding select list item is not updated.

In syntax 2, the requested columns are set to the specified values for the row at the current row of the specified query. The columns do not need to be in the select list of the specified open cursor. This format can be prepared.

Also, when assigning a variable, the variable must already be declared, and its name must begin with the "at" sign (@). Variable and column assignments can be mixed together, and any number can be used. If a name on the left side of an assignment in the SET list matches a column in the updated table and the variable name, the statement updates the column.

The USING DESCRIPTOR, FROM hostvar-list, and hostvar formats are for embedded SQL only.

Permissions

Must have UPDATE permission on the columns being modified.

Side effects

None.

Standards and compatibility

SQL/2003 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.

Example

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

UPDATE Employees
SET Surname = 'Jones'
WHERE CURRENT OF emp_cursor;

Discuss this page in DocCommentXchange.
Send feedback about this page using email.