ASA SQL Reference
  SQL Statements

UPDATE (positioned) statement [ESQL] [SP]

Description 

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 table-list
SET set-item, ...
WHERE CURRENT OF cursor-name

hostvar-list : indicator variables allowed

set-item :
  column-name [.field-name...] = expression
| column-name [.field-name...].method-name( [ expression ] )

sqlda-name : identifier

Parameters 

SET clause    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 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 cannot contain aggregate functions, subqueries, or subselects.

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 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.

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.

See also 

DELETE statement

DELETE (positioned) statement [ESQL] [SP]

UPDATE statement

Standards and compatibility 

• SQL/92    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.

• SQL/99    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 Adaptive Server Anywhere.

Example 

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

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