The UPDATE statement modifies the data contained in a particular row or set of rows. There are two forms, positioned UPDATE and searched UPDATE.
Syntax
|
table_specification |
A qualified or unqualified identifier which refers to the table to be amended. The table must be defined at this statement's compilation time. If the table specification is a view reference this view must be updatable. See Common Elements, section Table Specification for more details. |
|
correlation_identifier |
Allows the table to be referenced by another identifier. See Common Elements , section Correlation Identifier for more details. |
|
expression |
Specifies the new values to which the columns in the row(s) under consideration will be assigned. See Common Elements, section Row Amendment Expression for more details. |
|
WHERE CURRENT OF cursor identifier |
Indicates that the UPDATE is positioned. The cursor identifier refers to a cursor which is currently open and pointing to a row. |
|
WHERE_search_condition |
Indicates that the UPDATE statement is searched. Omission of the WHERE clause equates to a special case of a searched UPDATE statement. See Search Condition. |
An UPDATE statement modifies the columns of the rows identified in the WHERE clause with the values specified in the row amendment expression.
Updates of key column values in the master table will be cascaded to the related subtables. All other columns of a subtable can be updated with new values as usual, provided that the values of foreign keys and SEQNOs remain the same as already stored.
If the UPDATE statement is positioned, then the UPDATE is only applied to the row to which the cursor is currently pointing. The cursor must be open and pointing to a row otherwise a runtime error will occur. In addition, the cursor must be updatable. See DECLARE CURSOR for further details. Updating does not alter the position of the cursor. In addition, any locks on the row are not released until either a COMMIT or a ROLLBACK statement is executed.
Alternatively, in case of a searched UPDATE statement, a resultant table is established at execution time in a similar manor to a query specification. The UPDATE, then occurs for each row in the resultant table as specified by the row amendment expression . All the rows of the resultant table are locked and are not released until either a COMMIT or a ROLLBACK is executed. If no rows are identified for updating, then the field SQLCODE of the SQLCA will be set to +100.
An UPDATE statement without a WHERE clause is really a special case of the searched alternative as a resultant table is established which contains all the rows of the target table.
If the table referenced is a view, then this view must be updatable.
(Adabas only) For reasons of enforcing referential integrity it is not possible to change the value of foreign key columns in level 1 or level 2 tables. In a clustered environment this would require to physically move a row to a new location.
Restrictions which apply when updating views can be found in the Limitation section of the CREATE VIEW statement description.
(Adabas only) A SEQNO column is not updatable. The SEQNO columns map to the information that is used for internal Adabas addressing, and no rows will be moved to a new location using an UPDATE statement.
(Adabas only) An empty string or zero value can not be inserted into columns which have been defined with SUPPRESSION (i.e. the Adabas NU option) and with the NULL capability, as these two values are actually not representable under this condition. Same applies to columns with just the NULL capability, as the empty string or zero value represent the NULL value.
The use of the VALUES format in the row amendment expression is not permitted.
A positioned UPDATE statement must appear in the same compilation unit as the associated DECLARE and OPEN statements and must appear physically after the DECLARE statement.
The use of correlation identifiers in this context is not supported in ANSI compatibility mode.
The use of correlation identifiers is permitted.
Example
The following example updates all prices in the cruise table by adding 100 to the original cost.
UPDATE cruise
SET cruise_price = cruise_price + 100 ;
The following example decreases customer 816 amount to pay by 100.
UPDATE contract
SET amount_payment = amount_payment - 100
WHERE id_customer = 816;