UPDATE

The UPDATE statement updates the values of specified columns in rows of a table or view. Updating a row of a view updates a row of its base table, if no INSTEAD OF UPDATE trigger is defined on this view. If such a trigger is defined, the trigger will be activated instead.

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 is used to update exactly one row (as determined by the current position of a cursor).

Invocation

A Searched UPDATE statement can be embedded in an application program or issued interactively. A Positioned UPDATE must be embedded in an application program. Both forms are executable statements that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

  • For the table or view identified in the statement:
    • The UPDATE privilege on the table or view, or
    • The UPDATE privilege on each column to be updated; and
    • The system authority *EXECUTE on the library containing the table or view
  • Database administrator authority

If the expression in the assignment-clause contains a reference to a column of the table or view, or if the search-condition in a Searched UPDATE contains a reference to a column of the table or view, then the privileges held by the authorization ID of the statement must also include one of the following:

  • The SELECT privilege on the table or view
  • security administrator authority

If the search-condition includes a subquery or if the assignment-clause includes a scalar-fullselect or row-fullselect, see Queries for an explanation of the authorization required for each subselect.

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.

Syntax

Searched UPDATE:
Read syntax diagramSkip visual syntax diagramUPDATEtable-nameview-namecorrelation-clauseOVERRIDING SYSTEM VALUEOVERRIDING USER VALUESETassignment-clauseWHEREsearch-condition order-by-clause offset-clause fetch-clause isolation-clauseconcurrent-access-resolution-clause
Positioned UPDATE:
Read syntax diagramSkip visual syntax diagramUPDATEtable-nameview-namecorrelation-clauseOVERRIDING SYSTEM VALUEOVERRIDING USER VALUESETassignment-clauseWHERE CURRENT OFcursor-name
assignment-clause
Read syntax diagramSkip visual syntax diagram,column-name(column-name) = expressionNULLDEFAULT(,column-name) = (,expressionNULLDEFAULTrow-fullselect)ROW = (,expressionNULLDEFAULTrow-fullselect)
order-by-clause
Read syntax diagramSkip visual syntax diagram ORDER BY ,sort-key-expressionASCDESC
isolation-clause
Read syntax diagramSkip visual syntax diagramWITHNCURCSRSRR

Description

table-name or view-name
Identifies the table or view to be updated. The name must identify a table or view that exists at the current server, but it must not identify Start of changea history table,End of change a catalog table, a view of a catalog table, or a read-only view. For an explanation of read-only views and updatable views, see CREATE VIEW.
correlation-clause
Can be used within search-condition or assignment-clause to designate the table or view. For an explanation of correlation-clause, see table-reference. For an explanation of correlation-name, see Correlation names.
OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE
Specifies whether system-generated values or user-specified values for a ROWID, identity, or row change timestamp column are used. If OVERRIDING SYSTEM VALUE is specified, the implicit or explicit list of columns in the SET clause must contain a Start of changeROWID, identity, or row change timestampEnd of change column defined as GENERATED ALWAYS. If OVERRIDING USER VALUE is specified, the implicit or explicit list of columns in the SET clause must contain a column defined as either GENERATED ALWAYS or GENERATED BY DEFAULT.
OVERRIDING SYSTEM VALUE
Specifies that the value specified in the SET clause for a Start of changeROWID, identity, or row change timestampEnd of change column that is defined as GENERATED ALWAYS is used. A system-generated value is not used.
Start of changeIf a value for a row-begin, row-end, transaction-start-ID, or generated expression column is provided, it must be DEFAULT.End of change
OVERRIDING USER VALUE
Specifies that the value specified in the SET clause for a column that is defined as either GENERATED ALWAYS or GENERATED BY DEFAULT is ignored. Instead, a system-generated value is used, overriding the user-specified value.

If neither OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE is specified:

  • A value cannot be specified for a ROWID, identity, row change timestamp, Start of changerow-begin, row-end, transaction-start-ID, or generated expressionEnd of change column that is defined as GENERATED ALWAYS.
  • A value can be specified for a ROWID column that is defined as GENERATED BY DEFAULT. If a value is specified, that value is assigned to the column. However, a value in a ROWID column defined BY DEFAULT can be updated only if the specified value is a valid row ID value that was previously generated by DB2® for z/OS® or Db2® for i.
  • A value can be specified for an identity or row change timestamp column that is defined as GENERATED BY DEFAULT. When a value of an identity column or row change timestamp column defined BY DEFAULT is updated, the database manager does not verify that the specified value is a unique value for the column unless the identity column or row change timestamp column is the sole key in a unique constraint or unique index. Without a unique constraint or unique index, the database manager can guarantee unique values only among the set of system-generated values as long as NO CYCLE is in effect.

    If a value is not specified the database manager generates a new value.

SET
Introduces the assignment of values to column names.
assignment-clause
column-name
Identifies a column to be updated. The column-name must identify a column of the specified table or view. If extended indicators are not enabled, that column must be an updatable column. The same column name must not be specified more than once.

For a Positioned UPDATE:

  • If the UPDATE clause was specified in the SELECT statement of the cursor, each column name in the SET list must also appear in the UPDATE clause.
  • If the UPDATE clause was not specified in the SELECT statement of the cursor, the name of any updatable column may be specified.

For more information, see update-clause.

A view column derived from the same column as another column of the view can be updated, but both columns cannot be updated in the same UPDATE statement.

If a list of column-names is specified, the number of expressions, NULLs, and DEFAULTS must match the number of column-names.

ROW
Identifies all the columns of the specified table or view except for columns defined with the hidden attribute. If a view is specified, none of the columns of the view may be derived from a scalar function, constant, or expression.

The number of expressions, NULLs, and DEFAULTs (or the number of result columns from a row-fullselect) must match the number of columns in the row.

For a Positioned UPDATE, if the UPDATE clause was specified in the SELECT statement of the cursor, each column of the table or view must also appear in the UPDATE clause. For more information, see update-clause.

ROW may not be specified for a view that contains a view column derived from the same column as another column of the view, because both columns cannot be updated in the same UPDATE statement.

expression
Specifies the new value of the column. The expression is any expression of the type described in Expressions. It must not include an aggregate function.

A column-name in an expression must name a column of the named table or view. For each row updated, the value of the column in the expression is the value of the column in the row before the row is updated.

Each variable in the clause must identify a host structure or variable that is declared in accordance with the rules for declaring host structures and variables. In the operational form of the statement, a reference to a host structure is replaced by a reference to each of its variables. If expression is a single host variable, the host variable can include an indicator with an extended indicator value. If extended indicators are enabled and an expression in the assignment clause is not a single host variable, the extended indicator values of DEFAULT and UNASSIGNED must not be used. For further information about variables and structures, see References to host variables and Host structures. If a host structure is specified, the keyword ROW must be specified.

NULL
Specifies the new value for a column is the null value. NULL should only be specified for nullable columns.
DEFAULT
Specifies that the default value is assigned to a column. The value that is used depends on how the column was defined, as follows:
  • Start of changeIf the column is defined as a generated column based on an expression, the column value will be generated by the database manager, based on the expression.End of change
  • If the column is a ROWID, identity column, Start of changerow-begin column, row-end column, or transaction-start-ID columnEnd of change, the database manager will generate a new value.
  • If the WITH DEFAULT clause is used, the default used is as defined for the column (see default-clause in column-definition in CREATE TABLE).
  • If the WITH DEFAULT clause or the NOT NULL clause is not used, the value used is NULL.
  • If the column is defined as a row change timestamp column, a new row change timestamp value is assigned to the column.

If the NOT NULL clause is used and the WITH DEFAULT clause is not used, or if DEFAULT NULL is used, the DEFAULT keyword cannot be specified for that column.

DEFAULT must be specified for an identity column defined as GENERATED ALWAYS unless OVERRIDING SYSTEM VALUE is used.

Start of changeThe only value that a row-begin, row-end, transaction-start-ID, or generated expression column can be set to is DEFAULT.End of change

row-fullselect
A fullselect that returns a single result row. The number of result columns in the select list must match the number of column-names (or if ROW is specified, the number of columns in the row) specified for assignment. The result column values are assigned to each corresponding column-name. If the result of the fullselect is no rows, then null values are assigned. An error is returned if there is more than one row in the result.

The row-fullselect may contain references to columns of the target table of the UPDATE statement. For each row updated, the value of such a column in the expression is the value of the column in the row before the row is updated.

WHERE
Specifies the rows to be updated. The clause can be omitted, or a search-condition or cursor-name can be specified. If the clause is omitted, all rows of the table or view are updated.
search-condition
Is any search described in Search conditions. Each column-name in the search condition, other than in a subquery, must name a column of the table or view. When the search condition includes a subquery in which the same table is the base object of both the UPDATE and the subquery, the subquery is completely evaluated before any rows are updated.

The search-condition is applied to each row of the table or view. The updated rows are those for which the results of the search-condition are true.

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 of that subquery used in applying the search-condition. In actuality, a subquery with no correlated references may be executed only once. A subquery with a correlated reference may have to be executed once for each row.

CURRENT OF cursor-name
Identifies the cursor to be used in the update operation. The cursor-name must identify a declared cursor as explained in DECLARE CURSOR.

The table or view named must also be named in the FROM clause of the SELECT statement of the cursor, and the result table of the cursor must not be read-only. For an explanation of read-only result tables, see DECLARE CURSOR.

Start of changeThe DECLARE CURSOR statement must not have a period-specification for the table or view used by the update statement.End of change

When the UPDATE statement is executed, the cursor must be positioned on a row; that row is updated.

Start of changeorder-by-clauseEnd of change
Start of changeSpecifies the order of the rows for application of the offset-clause and fetch-clause. An order-by-clause should be specified to ensure a predictable order for determining the set of rows to be updated based on the offset-clause and fetch-clause.
sort-key-expression
An expression that specifies the value that is to be used to order the rows that qualify for the update operation. If a single sort-key-expression is identified, the rows are ordered by the values of that sort-key-expression. If more than one sort-key-expression is identified, the rows are ordered by the values of the first sort-key-expression, then by the values of the second sort-key-expression, and so on.

The result of the sort-key-expression must not be DATALINK or XML.

ASC
Uses the values of the sort-key-expression in ascending order. This is the default.
DESC
Uses the values of the sort-key-expression in descending order.
End of change
Start of changeOrdering is performed in accordance with the comparison rules described in Language elements. The null value is higher than all other values. If your ordering specification does not determine a complete ordering, rows with duplicate values of the last identified sort-key-expression have an arbitrary order. If you do not specify ORDER BY, the rows to be updated have an arbitrary order. End of change
Start of changeoffset-clauseEnd of change
Start of changeLimits the effect of the update by skipping a subset of the qualifying rows. For more information on the offset-clause, see offset-clause.End of change
Start of changefetch-clauseEnd of change
Start of changeLimits the effect of the update to a subset of the qualifying rows. For more information on the fetch-clause, see fetch-clause.End of change
isolation-clause
Specifies the isolation level to be used for this statement.
WITH
Introduces the isolation level, which may be one of:
  • RR Repeatable read
  • RS Read stability
  • CS Cursor stability
  • UR Uncommitted read
  • NC No commit
If isolation-clause is not specified the default isolation is used. See isolation-clause for a description of how the default is determined.
concurrent-access-resolution-clause
Specifies the concurrent access resolution to use for the select statement. For more information, see concurrent-access-resolution-clause.

UPDATE Rules

Assignment: Update values are assigned to columns in accordance with the storage assignment rules described in Assignments and comparisons.

Validity: Updates must obey the following rules. If they do not, or if any other errors occur during the execution of the UPDATE statement, no rows are updated.

  • Fullselects: The row-fullselect or scalar-fullselect shall return no more than one row (SQLSTATE 21000).
  • Unique constraints and unique indexes: If the identified table, or the base table of the identified view, has one or more unique indexes or unique constraints, each row update in the table must conform to the limitations imposed by those indexes and constraints (SQLSTATE 23505).

    All uniqueness checks are effectively made at the end of the statement. In the case of a multiple-row UPDATE statement of a column involved in a unique index or unique constraint, this would occur after all rows were updated.

  • Check constraints: If the identified table, or the base table of the identified view, has one or more check constraints, each check constraint must be true or unknown for each row updated in the table (SQLSTATE 23513).

    All check constraints are effectively validated at the end of the statement. In the case of a multiple-row UPDATE statement, this would occur after all rows were updated.

  • Views and the WITH CHECK OPTION: If a view is identified, the updated rows must conform to any applicable WITH CHECK OPTION (SQLSTATE 44000). For more information, see CREATE VIEW.

Triggers: If the identified table or the base table of the identified view has an update trigger, the trigger is activated. A trigger might cause other statements to be executed or raise error conditions based on the updated values.

Referential Integrity: The value of the parent key in a parent row must not be changed.

If the update values produce a foreign key that is nonnull, the foreign key must be equal to some value of the parent key of the parent table of the relationship.

The referential constraints (other than a referential constraint with a RESTRICT delete rule) are effectively checked at the end of the statement. In the case of a multiple-row UPDATE statement, this would occur after all rows were updated.

XML values: When an XML column is updated, the new value must be a well-formed XML document.

Updating rows in a table for which row or column access control is enforced: When an UPDATE statement is issued for a table for which row or column access control is enforced, the rules specified in the enabled row permissions or column masks determine whether the row can be updated. Typically those rules are based on the authorization ID of the statement. The following describes how enabled row permissions and column masks are used during UPDATE:
  • Row permissions are used to identify the set of rows to be updated.

    When multiple enabled row permissions are defined for a table, a row access control search condition is derived by application of the logical OR operator to the search condition in each enabled permission. This row access control search condition is applied to the table to determine which rows are accessible to the authorization ID of the UPDATE statement. If the WHERE clause is specified in the UPDATE statement, the user-specified predicates are applied on the accessible rows to determine the rows to be updated. If there is no WHERE clause, all the accessible rows are the rows to be updated.

  • If there are rows to be updated, the following rules determine whether those rows can be updated:
    • When a column is referenced while deriving the values of a new row, if the column has an enabled column mask, the masked value is used to derive the new values. If the object table also has column access control activated, the column mask that is applied to derive the new values must return the column itself, not a constant or an expression. If the column mask does not mask the column to itself, the new value cannot be used for update and an error is returned.
    • If the rows are updatable, and there is a BEFORE UPDATE trigger for the table, the trigger is activated.

      Within the trigger actions, the new values for update might be modified in transition variables. When the final values are returned from the trigger, the new values are used for the update.

    • The rows that are to be updated must conform to the enabled row permissions:

      For each row that is to be updated, the old values are replaced with the new values that were specified in the UPDATE statement. A row that conforms to the enabled row permissions is a row that, if updated, can be retrieved using the derived row access control search condition.

    • If the rows are updatable, and there is an AFTER UPDATE trigger for the table, the trigger is activated.

Masked data can be assigned to a variable used as a value for the update operation. If an update violation check constraint does not exist for the column, the masked data will be updated into the column and no error will be issued.

Updating a partitioning key of a partitioned table: If a partitioning key of a row of a partitioned table is updated such that the row belongs in a different partition:
  • The specified target table must not be an alias that references a single partition of the table.
  • The specified target table must be journaled.

Extended indicator usage: When extended indicators are enabled, indicator values other than positive values and 0 (zero) through -7 must not be specified. The DEFAULT and UNASSIGNED extended indicator values must not appear in contexts in which they are not supported.

Extended indicators: In the assignment-clause of an UPDATE statement, an expression that is a reference to a single host variable can result in assigning an extended indicator value. Specifying an indicator value with the extended indicator value of UNASSIGNED has the same effect as if the column had not been specified in the assignment-clause. Assigning an extended indicator value of DEFAULT assigns the default value to the column, and must only be specified for a column that is defined with a default value.

If a target column is not updatable, for example an identity column defined as GENERATED ALWAYS, it must be assigned the extended indicator variable value of UNASSIGNED, Start of changeunless it is a generated column defined as GENERATED ALWAYS. If the target column is a generated column defined as GENERATED ALWAYS, then it must be assigned the extended indicator variable value of DEFAULT or UNASSIGNED.End of change

An UPDATE statement must not specify the extended indicator value of UNASSIGNED for all target columns.

Extended indicators and update triggers: If the indicator value for a target column is UNASSIGNED, that column is not considered to have been updated.

Extended indicators and deferred error checks: When extended indicators are enabled, validation that would otherwise be done during statement preparation to recognize an update of a non-updatable column is deferred until the statement is executed.

Start of change
Considerations for a system-period temporal table: When a row of a system-period temporal table is updated, the database manager updates the values of the row-begin and transaction-start-ID columns as follows:
  • A row-begin column is assigned a value that is generated using a reading of the time-of-day clock during execution of the first data change statement in the transaction that requires a value to be assigned to the row begin or transaction start-ID column in a table, or a row in a system-period temporal table is deleted. The database manager ensures uniqueness of the generated values for a row-begin column across transactions. The timestamp value might be adjusted to ensure that rows inserted into an associated history table have the end timestamp value greater than the begin timestamp value which can happen when a conflicting transaction is updating the same row in the system-period temporal table. The SYSTIME_PERIOD_ADJ QAQQINI option must be set to *ADJUST for this adjustment to the timestamp value to occur. If multiple rows are updated within a single SQL transaction and an adjustment is not needed, the values for the row-begin column are the same for all the rows and are unique from the values generated for the column for another transaction.
  • A transaction start-ID column is assigned a unique timestamp value per transaction or the null value The null value is assigned to the transaction start-ID column if the column is nullable and there is a row-begin column in the table for which the value did not need to be adjusted. Otherwise, the value is generated using a reading of the time-of-day clock during execution of the first data change statement in the transaction that requires a value to be assigned to the row begin or transaction start-ID column in a table, or a row in a system-period temporal table is deleted. If multiple rows are updated within a single SQL transaction, the values for the transaction start-ID column are the same for all the rows and are unique from the values generated for the column for another transaction.

If the UPDATE statement has a search condition containing a correlated subquery that references historical rows (explicitly referencing the name of the history table name or implicitly through the use of a period specification in the FROM clause), the old version of the updated rows that are inserted as historical rows (into the history table if any) are potentially visible to update operations for the rows subsequently processed for the statement.

If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value and the value of the SYSTIME option is YES, the underlying target (direct or indirect) of the UPDATE statement cannot be a system-period temporal table.

If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value and the value of the SYSTIME option is YES, the target of an UPDATE statement cannot be a view defined with the WITH CHECK OPTION if the view definition includes a WHERE clause containing an invocation of an external routine with a data access indication other than NO SQL.

End of change
Start of change

Considerations for a history table: When a row of a system-period temporal table is updated, a historical copy of the row is inserted into the corresponding history table and the end timestamp of the historical row is captured in the form of a system determined value that corresponds to the time of the data change operation. The database manager assigns the value that is generated using a reading of the time-of-day clock during execution of the first data change statement in the transaction that requires a value to be assigned to the row begin or transaction start-ID column in a table, or a row in a system-period temporal table is deleted. The database manager ensures uniqueness of the generated values for an end column in a history table across transactions. The timestamp value might be adjusted to ensure that rows inserted into the history table have the end timestamp value greater than the begin timestamp value which can happen when a conflicting transaction is updating the same row in the system-period temporal table. The SYSTIME_PERIOD_ADJ QAQQINI option must be set to *ADJUST for this adjustment to the timestamp value to occur. Otherwise, an error is returned.

For an update operation, the adjustment only affects the value for the end column corresponding to the row-end column in the history table associated with the system-period temporal table. Take these adjustments into consideration on subsequent references to the table whether there is a search for the transaction start time in the values for the columns corresponding to the row-begin and row-end columns of the period in the associated system-period temporal table.

End of change

Notes

Update operation errors: If an update value violates any constraints, or if any other error occurs during the execution of the UPDATE statement and COMMIT(*NONE) was not specified, all changes made during the execution of the statement are backed out. However, other changes in the unit of work made prior to the error are not backed out. If COMMIT(*NONE) is specified, changes are not backed out.

It is possible for an error to occur that makes the state of the cursor unpredictable.

Number of rows updated: When an UPDATE statement completes execution, the number of rows updated is returned in the ROW_COUNT statement information item in the SQL Diagnostics Area (and SQLERRD(3) in the SQLCA). For a description of the SQLCA, see SQLCA (SQL communication area).

For a description of ROW_COUNT, see GET DIAGNOSTICS. For a description of the SQLCA, see SQLCA (SQL communication area).

Locking: Unless appropriate locks already exist, one or more exclusive locks are acquired by the execution of a successful UPDATE statement. Until these locks are released by a commit or rollback operation, the updated rows can only be accessed by:

  • The application process that performed the update.
  • Another application process using COMMIT(*NONE) or COMMIT(*CHG) through a read-only operation

The locks can prevent other application processes from performing operations on the table. For further information about locking, see the description of the COMMIT, ROLLBACK, and LOCK TABLE statements, and isolation levels in Isolation level. Also, see the Database Programming topic collection.

A maximum of 500 000 000 rows can be updated or changed in any single UPDATE statement when COMMIT(*RR), COMMIT(*ALL), COMMIT(*CS), or COMMIT(*CHG) has been specified. The number of rows changed includes any rows inserted, updated, or deleted under the same commitment definition as a result of a trigger.

REXX: Variables cannot be used in the UPDATE statement within a REXX procedure. Instead, the UPDATE must be the object of a PREPARE and EXECUTE using parameter markers.

Datalinks: If the URL value of a DATALINK column is updated, this is the same as deleting the old DATALINK value then inserting the new one. First, if the old value was linked to a file, that file is unlinked. Then, unless the linkage attributes of the DATALINK value are empty, the specified file is linked to that column.

The comment value of a DATALINK column can be updated without relinking the file by specifying an empty string as the URL path (for example, as the data-location argument of the DLVALUE scalar function or by specifying the new value to be the same as the old value). If a DATALINK column is updated with a null, it is the same as deleting the existing DATALINK value.

An error may occur when attempting to update a DATALINK value if the file server of either the existing value or the new value is no longer registered with the database server

Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:

  • The keyword NONE can be used as a synonym for NC.
  • The keyword CHG can be used as a synonym for UR.
  • The keyword ALL can be used as a synonym for RS.

Examples

Example 1: Change the job (JOB) of employee number (EMPNO) ‘000290' in the EMPLOYEE table to ‘LABORER'.

  UPDATE EMPLOYEE
    SET JOB = 'LABORER'
    WHERE EMPNO = '000290'

Example 2: Increase the project staffing (PRSTAFF) by 1.5 for all projects that department (DEPTNO) ‘D21' is responsible for in the PROJECT table.

  UPDATE PROJECT
    SET PRSTAFF = PRSTAFF + 1.5
    WHERE DEPTNO = 'D21'

Example 3: All the employees except the manager of department (WORKDEPT) ‘E21' have been temporarily reassigned. Indicate this by changing their job (JOB) to NULL and their pay (SALARY, BONUS, COMM) values to zero in the EMPLOYEE table.

  UPDATE EMPLOYEE
    SET JOB=NULL, SALARY=0, BONUS=0, COMM=0
    WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'

Example 4: In a Java program display the rows from the EMPLOYEE table on the connection context 'ctx' and then, if requested to do so, change the job (JOB) of certain employees to the new job keyed in (NEWJOB).

  #sql iterator empIterator implements sqlj.runtime.ForUpdate
       with( updateColumns='JOB' )
       ( … );
  empIterator C1;

  #sql [ctx] C1 = { SELECT * FROM EMPLOYEE };

  #sql { FETCH :C1 INTO … };
  while ( !C1.endFetch() )  {
     System.out.println( … );
                 …
     if ( condition for updating row ) {
         #sql [ctx] { UPDATE EMPLOYEE
                        SET JOB = :NEWJOB
                        WHERE CURRENT OF :C1 };
     }

     #sql { FETCH :C1 INTO … };
  }
  C1.close();