UPDATE

The UPDATE statement updates the values of specified columns in rows of a table.

There are two forms of this statement:

  • The searched UPDATE form is used to update one or more rows optionally determined by a search condition.
  • The positioned UPDATE form specifies that one or more rows corresponding to the current cursor position are to be updated.

searched update:

positioned update:

include-column:

data-type:

built-in-type:

assignment clause:

Notes:

  • 1 The number of expressions, DEFAULT, and NULL keywords must match the number of column-names. Expressions must not refer to UNPACK-function-invocation..
  • 3 The number of items returned from UNPACK-function-invocation must match the number of column names.
Description
table-name
Identifies the object of the UPDATE statement. The name must identify a table that exists at the Data Virtualization Manager server that is identified by the implicitly or explicitly specified location name.
correlation-name
Can be used within search-condition or assignment-clause to designate the table.
include-column
Specifies a set of columns that are included, along with the columns of table-name, in the result table of the UPDATE statement when it is nested in the FROM clause of the outer fullselect that is used in a subselect, SELECT statement, or in a SELECT INTO statement. The included columns are appended to the end of the list of columns that is identified by table-name. If no value is assigned to a column that is specified by an include-column, a NULL value is returned for that column.
INCLUDE
Introduces a list of columns that are to be included in the result table of the UPDATE statement. The included columns are only available if the UPDATE statement is nested in the FROM clause of a SELECT statement or a SELECT INTO statement.
column-name
Specifies the name for a column of the result table of the UPDATE statement that is not the same name as another included column nor a column in the table that is specified in table-name.
data-type
Specifies the data type of the included column. The included columns are nullable.
built-in-type
Specifies a built-in data type.

The CCSID 1208 and CCSID 1200 clauses must not be specified for an INCLUDE column.

distinct-type
Specifies a distinct type. Any length, precision, or scale attributes for the column are those of the source type of the distinct type as specified by using the CREATE TYPE statement.
SET
Introduces the assignment of values to column names.
assignment-clause
If row-fullselect is specified, the number of columns in the result of row-fullselect must match the number of column-names that are specified. If row-fullselect is not specified, the number of expressions must match the number of column-names that are specified.
column-name
Identifies a column that is to be updated. column-name must identify a column of the specified table, and that column must be updatable if extended indicator variables are not enabled. The column must not identify a generated column where the column is derived from a scalar function, constant, or expression. column-name can also identify an INCLUDE column that must not be qualified. The same column must not be specified more than one time.
expression
Indicates the new value of the column.
DEFAULT
Specifies that the default value is used based on how the corresponding column is defined in the table.
UNPACK-function-invocation
Specifies an invocation of the UNPACK built-in function. The number of fields that are returned by the UNPACK function invocation must be the same as the number of column-names.
WHERE
Specifies the rows to be updated. You can omit the clause, give a search condition, or specify a cursor. If you omit the clause, all rows of the table are updated.
search-condition
Specifies any search condition described in Language elements. Each column-name in the search condition, other than in a subquery, must identify a column of the table.

The search-condition is applied to each row of the table and the updated rows are those for which the result of the search-condition is true. If the unique key or primary key is a parent key, the constraints are effectively checked at the end of the operation.

If the search condition contains a subquery, the subquery can be thought of as being executed each time the search condition is applied to a row, and the results used in applying the search condition. In actuality, a subquery with no correlated references is executed just once, whereas it is possible that a subquery with a correlated reference must be executed once for each row.

WHERE CURRENT OF cursor-name
Identifies the cursor to be used in the update operation. cursor-name must identify a declared cursor as explained in the description of the DECLARE CURSOR statement in DECLARE CURSOR. If the UPDATE statement is embedded in a program, the DECLARE CURSOR statement must include select-statement rather than statement-name.

The object of the UPDATE statement must also be identified in the FROM clause of the SELECT statement of the cursor. The columns to be updated can be identified in the FOR UPDATE clause of that SELECT statement though they do not have to be identified. If the columns are not specified, the columns that can be updated include all the updatable columns of the table that is identified in the first FROM clause of the fullselect.

The result table of the cursor must not be read-only. For an explanation of read-only result tables, see Read-only cursors. Note that the object of the UPDATE statement must not be identified as the object of the subquery in the WHERE clause of the SELECT statement of the cursor.

When the UPDATE statement is executed, the cursor must be open and positioned on a row or rowset of the result table.

  • If the cursor is positioned on a single row, that row is the one updated.
  • If the cursor is positioned on a rowset, all rows corresponding to the rows of the current rowset are updated.

FOR ROW n OF ROWSET
Specifies which row of the current rowset is to be updated. The corresponding row of the rowset is updated, and the cursor remains positioned on the current rowset.

host-variable or integer-constant is assigned to an integral value k. If host-variable is specified, it must be an exact numeric type with scale zero, must not include an indicator variable, and k must be in the range 1 - 32767.

The cursor must be positioned on a rowset, and the specified value must be a valid value for the set of rows most recently retrieved for the cursor. If the specified row cannot be updated, an error is returned. It is possible that the specified row is within the bounds of the rowset most recently requested, but the current rowset contains less than the number of rows that were implicitly or explicitly requested when that rowset was established.

If this clause is not specified, the cursor position determines the rows that will be affected. If the cursor is positioned on a single row, that row is the one updated. In the case where the most recent FETCH statement returned multiple rows of data (but not as a rowset), this position would be on the last row of data that was returned. If the cursor is positioned on a rowset, all rows corresponding to the current rowset are updated. The cursor position remains unchanged.

It is possible for another application process to update a row in the base table of the SELECT statement so that the specified row of the cursor no longer has a corresponding row in the base table. An attempt to update such a row results in an error.

Datetime representation when using datetime registers:
As explained under Datetime special registers, when two or more datetime registers are implicitly or explicitly specified in a single SQL statement, they represent the same point in time. This is also true when multiple rows are updated.
Other SQL statements in the same unit of work:
The following statements cannot follow an UPDATE statement in the same unit of work:
  • An INSERT, UPDATE, or DELETE statement that updates accelerator-only tables from a different accelerator.
Examples
Example 1
Change employee 000190's telephone number to 3565 in a sample table EMP.

   UPDATE EMP
     SET PHONENO='3565'
     WHERE EMPNO='000190';
Example 2
Give each member of department D11 a 100-dollar raise.

  UPDATE EMP
    SET SALARY = SALARY + 100
    WHERE WORKDEPT = 'D11';
Example 3
Employee 000250 is going on a leave of absence. Set the employee's pay values (SALARY, BONUS, and COMMISSION) to null.

   UPDATE EMP
     SET SALARY = NULL, BONUS = NULL, COMM = NULL
     WHERE EMPNO='000250';