UPDATE statement

Invocation

An UPDATE statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

If the specified table or view is preceded by the ONLY keyword, the privileges held by the authorization ID of the statement must also include the SELECT privilege for every subtable or subview of the specified table or view, or SELECTIN privilege on the schema containing the subtables or subviews of the specified table or view.

GROUP privileges are not checked for static UPDATE statements.

If the target of the update operation is a nickname, privileges on the object at the data source are not considered until the statement is executed at the data source. At this time, the authorization ID that is used to connect to the data source must have the privileges that are required for the operation on the object at the data source. The authorization ID of the statement can be mapped to a different authorization ID at the data source.

Description

table-name, view-name, nickname, or (fullselect)

Identifies the object of the update operation. The name must identify one of the following objects:

• A table, view, or nickname described in the catalog at the current server
• A table or view at a remote server specified using a remote-object-name

The object must not be a catalog table, a view of a catalog table (unless it is one of the updatable SYSSTAT views), a system-maintained materialized query table, or a read-only view that has no INSTEAD OF trigger defined for its update operations.

If table-name is a typed table, rows of the table or any of its proper subtables may get updated by the statement. Only the columns of the specified table may be set or referenced in the WHERE clause. For a positioned UPDATE, the associated cursor must also have specified the same table, view or nickname in the FROM clause without using ONLY.

If the object of the update operation is a fullselect, the fullselect must be updatable, as defined in the Updatable views Notes item in the description of the CREATE VIEW statement.

If the object of the update operation is a nickname, the extended indicator variable values of DEFAULT and UNASSIGNED must not be used (SQLSTATE 22539).

For additional restrictions related to temporal tables and use of a view or fullselect as the target of the update operation, see Considerations for a system-period temporal table and Considerations for an application-period temporal table in the Notes section of this topic.

ONLY (table-name)

Applicable to typed tables, the ONLY keyword specifies that the statement should apply only to data of the specified table and rows of proper subtables cannot be updated by the statement. For a positioned UPDATE, the associated cursor must also have specified the table in the FROM clause using ONLY. If table-name is not a typed table, the ONLY keyword has no effect on the statement.

ONLY (view-name)

Applicable to typed views, the ONLY keyword specifies that the statement should apply only to data of the specified view and rows of proper subviews cannot be updated by the statement. For a positioned UPDATE, the associated cursor must also have specified the view in the FROM clause using ONLY. If view-name is not a typed view, the ONLY keyword has no effect on the statement.

period-clause

Specifies that a period clause applies to the target of the update operation. If the target of the update operation is a view, the following conditions apply to the view:

• The FROM clause of the outer fullselect of the view definition must include a reference, directly or indirectly, to an application-period temporal table (SQLSTATE 42724M).
• An INSTEAD OF UPDATE trigger must not be defined for the view (SQLSTATE 428HY).

FOR PORTION OF BUSINESS_TIME

Specifies that the update only applies to row values for the portion of the period in the row that is specified by the period clause. The BUSINESS_TIME period must exist in the table (SQLSTATE 4274M).

FROM value1 TO value2

Specifies that the update applies to rows for the period specified from value1 up to value2. No rows are updated if value1 is greater than or equal to value2, or if value1 or value2 is the null value (SQLSTATE 02000).

For the period specified with FROM value1 TO value2, the BUSINESS_TIME period in a row in the target of the update is in any of the following states:

• Overlaps the beginning of the specified period if the value of the begin column is less than value1 and the value of the end column is greater than value1.
• Overlaps the end of the specified period if the value of the end column is greater than or equal to value2 and the value of the begin column is less than value2.
• Is fully contained within the specified period if the value for the begin column for BUSINESS_TIME is greater than or equal to value1 and the value for the corresponding end column is less than or equal to value2.
• Is partially contained in the specified period if the row overlaps the beginning of the specified period or the end of the specified period, but not both.
• Fully overlaps the specified period if the period in the row overlaps the beginning and end of the specified period.
• Is not contained in the period if both columns of BUSINESS_TIME are less than or equal to value1 or greater than or equal to value2.

If the BUSINESS_TIME period in a row is not contained in the specified period, the row is not updated. Otherwise, the update is applied based on how the values in the columns of the BUSINESS_TIME period overlap the specified period as follows:

• If the BUSINESS_TIME period in a row is fully contained within the specified period, the row is updated and the values of the begin column and end column of BUSINESS_TIME are unchanged.
• If the BUSINESS_TIME period in a row is partially contained in the specified period and overlaps the beginning of the specified period:
  • The row is updated. In the updated row, the value of the begin column is set to value1 and the value of the end column is the original value of the end column.
  • A row is inserted using the original values from the row, except that the end column is set to value1.
• If the BUSINESS_TIME period in a row is partially contained in the specified period and overlaps the end of the specified period:
  • The row is updated. In the updated row, the value of the begin column is the original value of the begin column and the end column is set to value2.
  • A row is inserted using the original values from the row, except that the begin column is set to value2.
• If the BUSINESS_TIME period in a row fully overlaps the specified period:
  • The row is updated. In the updated row the value of the begin column is set to value1 and the value of the end column is set to value2.
  • A row is inserted using the original values from the row, except that the end column is set to value1.
  • An additional row is inserted using the original values from the row, except that the begin column is set to value2.

value1 and value2

Each expression must return a value that has a date data type, timestamp data type, or a valid data type for a string representation of a date or timestamp (SQLSTATE 428HY). The result of each expression must be comparable to the data type of the columns of the specified period (SQLSTATE 42884). See the comparison rules described in Assignments and comparisons.

Each expression can contain any of the following supported operands (SQLSTATE 428HY):

• Constant
• Special register
• Variable. For details, refer to References to variables.
• Scalar function whose arguments are supported operands (though user-defined functions and non-deterministic functions cannot be used)
• CAST specification where the cast operand is a supported operand
• Expression using arithmetic operators and operands

correlation-clause

Can be used within search-condition or assignment-clause to designate a table, view, nickname, or fullselect. For a description of correlation-clause, see table-reference in the description of Subselect.

include-columns

Specifies a set of columns that are included, along with the columns of table-name or view-name, in the intermediate result table of the UPDATE statement when it is nested in the FROM clause of a fullselect. The include-columns are appended at the end of the list of columns that are specified for table-name or view-name.

INCLUDE

Specifies a list of columns to be included in the intermediate result table of the UPDATE statement.

column-name

Specifies a column of the intermediate result table of the UPDATE statement. The name cannot be the same as the name of another include column or a column in table-name or view-name (SQLSTATE 42711).

data-type

Specifies the data type of the include column. The data type must be one that is supported by the CREATE TABLE statement.

SET

Introduces the assignment of values to column names.

assignment-clause

column-name

Identifies a column to be updated. If extended indicator variables are not enabled, the column-name must identify an updatable column of the specified table, view, or nickname, or identify an INCLUDE column. The object ID column of a typed table is not updatable (SQLSTATE 428DZ). A column must not be specified more than once, unless it is followed by ..attribute-name (SQLSTATE 42701).

If it specifies an INCLUDE column, the column name cannot be qualified.

For a Positioned UPDATE:

• If the update-clause was specified in the select-statement of the cursor, each column name in the assignment-clause must also appear in the update-clause.
• If the update-clause was not specified in the select-statement of the cursor and LANGLEVEL MIA or SQL92E was specified when the application was precompiled, the name of any updatable column may be specified.
• If the update-clause was not specified in the select-statement of the cursor and LANGLEVEL SAA1 was specified either explicitly or by default when the application was precompiled, no columns may be updated.

..attribute-name

Specifies the attribute of a structured type that is set (referred to as an attribute assignment. The column-name specified must be defined with a user-defined structured type (SQLSTATE 428DP). The attribute-name must be an attribute of the structured type of column-name (SQLSTATE 42703). An assignment that does not involve the ..attribute-name clause is referred to as a conventional assignment.

expression

Indicates the new value of the column. The expression is any expression of the type described in Expressions. The expression cannot include an aggregate function except when it occurs within a scalar fullselect (SQLSTATE 42903).

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

An expression cannot contain references to an INCLUDE column. If expression is a single host variable, the host variable can include an indicator variable that is enabled for extended indicator variables. If extended indicator variables are enabled, the extended indicator variable values of default (-5) or unassigned (-7) must not be used (SQLSTATE 22539) if either of the following statements is true:

• The expression is more complex than a single host variable with explicit casts
• The target column has data type of structured type

NULL

Specifies the null value and can only be specified for nullable columns (SQLSTATE 23502). NULL cannot be the value in an attribute assignment (SQLSTATE 429B9) unless it is specifically cast to the data type of the attribute.

DEFAULT

Specifies that the default value should be used based on how the corresponding column is defined in the table. The value that is inserted depends on how the column was defined.

• If the column was defined as a generated column based on an expression, the column value will be generated by the system, based on the expression.
• If the column was defined using the IDENTITY clause, the value is generated by the database manager.
• If the column was defined using the WITH DEFAULT clause, the value is set to the default defined for the column (see default-clause in ALTER TABLE).
• If the column was defined using the NOT NULL clause and the GENERATED clause was not used, or the WITH DEFAULT clause was not used, or DEFAULT NULL was used, the DEFAULT keyword cannot be specified for that column (SQLSTATE 23502).
• If the column was defined using the ROW CHANGE TIMESTAMP clause, the value is generated by the database manager.

The only value that a generated column defined with the GENERATED ALWAYS clause can be set to is DEFAULT (SQLSTATE 428C9).

The DEFAULT keyword cannot be used as the value in an attribute assignment (SQLSTATE 429B9).

The DEFAULT keyword cannot be used as the value in an assignment for update on a nickname where the data source does not support DEFAULT syntax.

row-fullselect

Specifies a fullselect that returns a single row. The result column values are assigned to each corresponding column-name. If the fullselect returns no rows, the null value is assigned to each column; an error occurs if any column to be updated is not nullable. An error also occurs if there is more than one row in the result.

A row-fullselect may contain references to columns of the target table of the UPDATE statement. For each row that is updated, the value of such a column in an expression is the value of the column in the row before the row is updated. An error is returned if there is more than one row in the result (SQLSTATE 21000).

FROM

Specifies a list of source tables that supply values for assignment to target table columns. The source tables are implicitly inner joined with the target table with the WHERE clause specifying the join condition. The rows in the target table that satisfy the WHERE condition are updated with the values from the source table rows.

When an UPDATE statement specifies a FROM clause:

• The target of the update operation cannot be a nickname.
• The source for the update operation cannot be an analyze_table-expression (that is, the result of a data mining model) or a data-change-table-reference (that is, the result of a nested UPDATE, DELETE, or INSERT statement).

For a description of table-reference, see table-reference.

WHERE

Introduces a condition that indicates what rows are updated. You can omit the clause, give a search condition, or name a cursor. If the clause is omitted, all rows of the table, view or nickname are updated.

search-condition

Each column-name in the search condition, other than in a subquery, must name a column of the table, view or nickname. 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, view or nickname and the updated rows are those for which the result of the search-condition is 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 used in applying the search condition. In actuality, a subquery with no correlated references is executed only once, whereas 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, explained in DECLARE CURSOR. The DECLARE CURSOR statement must precede the UPDATE statement in the program.

The specified table, view, or nickname 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.)

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

This form of UPDATE cannot be used (SQLSTATE 42828) if the cursor references:

• A view on which an INSTEAD OF UPDATE trigger is defined
• A view that includes an OLAP function in the select list of the fullselect that defines the view
• A view that is defined, either directly or indirectly, using the WITH ROW MOVEMENT clause

order-by-clause

Specifies the order of the rows for application of the offset-clause and fetch-clause. Specify an order-by-clause to ensure a predictable order for determining the set of rows to be updated based on the offset-clause and fetch-clause. For details on the order-by-clause, see order-by-clause.

offset-clause

Limits the effect of the update by skipping a subset of the qualifying rows. For details on the offset-clause, refer to offset-clause.

fetch-clause

Limits the effect of the update to a subset of the qualifying rows. For details on the fetch-clause, refer to fetch-clause.

WITH

Specifies the isolation level at which the UPDATE statement is executed.

RR

Repeatable Read

RS

Read Stability

CS

Cursor Stability

UR

Uncommitted Read

The default isolation level of the statement is the isolation level of the package in which the statement is bound. The WITH clause has no effect on nicknames, which always use the default isolation level of the statement.

SKIP LOCKED DATA

The SKIP LOCKED DATA clause specifies that rows are skipped when incompatible locks that would block the progress of the statement are held on the rows by other transactions. These rows can belong to any accessed table addressed in the statement, including tables accessed in a subquery. This clause applies when the isolation level is CS or RS and is ignored when an isolation level of UR or RR is in effect. It applies to row and block level locks.

Invocation

SKIP LOCKED DATA is ignored if it is specified when WITH RR or WITH UR. The default isolation level of the statement depends on the isolation of the package or plan with which the statement is bound, and whether the result table is read-only. If the default isolation level of the statement is Repeatable Read or Uncommitted Read, then SKIP LOCKED DATA is ignored.

NOWAIT / WAIT <time sec>

The NOWAIT and WAIT clauses specify the number of seconds to wait for a lock before returning an error indicating that a lock cannot be obtained.

When using the WAIT clause, <time sec> is an integer between -1 and 32767.

Note: For NOWAIT and WAIT 0, locks are not waited for. If no lock is available at the time of the request, a -911 error is returned.

When a WAIT value of -1 is specified, lock timeout detection is turned off. In this situation a lock is waited for (if one is not available at the time of the request) until either of the following events occur:

• The lock is granted.
• A deadlock occurs.

Use of the NOWAIT and WAIT clauses overwrites the value of the LOCKTIMEOUT database configuration variable and the value of the CURRENT LOCK TIMEOUT special register for this update statement. This means that adding the NOWAIT/WAIT clause with a wait time value of t has the same effect as executing the update statement with a LOCKTIMEOUT value or CURRENT LOCK TIMEOUT value of t.

While the NOWAIT and WAIT clauses are not allowed for positioned updates and deletes, you can use them in the declaration of the cursor. When used in the cursor declaration, the specified wait time value is inherited by the statements that use this cursor.

Rules

• Triggers: UPDATE statements may cause triggers to be executed. A trigger may cause other statements to be executed, or may raise error conditions based on the update values. If an update operation on a view causes an INSTEAD OF trigger to fire, validity, referential integrity, and constraints will be checked against the updates that are performed in the trigger, and not against the view that caused the trigger to fire, or its underlying tables.
• Assignment: Update values are assigned to columns according to specific assignment rules.
• Validity: The updated row must conform to any constraints imposed on the table (or on the base table of the view) by any unique index on an updated column.

  If a view is used that is not defined using WITH CHECK OPTION, rows can be changed so that they no longer conform to the definition of the view. Such rows are updated in the base table of the view and no longer appear in the view.

  If a view is used that is defined using WITH CHECK OPTION, an updated row must conform to the definition of the view. For an explanation of the rules governing this situation, see CREATE VIEW.

• Check constraint: Update value must satisfy the check-conditions of the check constraints defined on the table.

  An UPDATE to a table with check constraints defined has the constraint conditions for each column updated evaluated once for each row that is updated. When processing an UPDATE statement, only the check constraints referring to the updated columns are checked.

• Referential integrity: The value of the parent unique keys cannot be changed if the update rule is RESTRICT and there are one or more dependent rows. However, if the update rule is NO ACTION, parent unique keys can be updated as long as every child has a parent key by the time the update statement completes. A non-null update value of a foreign key must be equal to a value of the primary key of the parent table of the relationship.
• XML values: When an XML column value is updated, the new value must be a well-formed XML document (SQLSTATE 2200M).
• Security policy: If the identified table or the base table of the identified view is protected with a security policy, the session authorization ID must have the label-based access control (LBAC) credentials that allow:
  • Write access to all protected columns that are being updated (SQLSTATE 42512)
  • Write access for any explicit value provided for a DB2SECURITYLABEL column for security policies that were created with the RESTRICT NOT AUTHORIZED WRITE SECURITY LABEL option (SQLSTATE 23523)
  • Read and write access to all rows that are being updated (SQLSTATE 42519)

  The session authorization ID must also have been granted a security label for write access for the security policy if an implicit value is used for a DB2SECURITYLABEL column (SQLSTATE 23523), which can happen when:

  • The DB2SECURITYLABEL column is not included in the list of columns that are to be updated (and so it will be implicitly updated to the security label for write access of the session authorization ID)
  • A value for the DB2SECURITYLABEL column is explicitly provided but the session authorization ID does not have write access for that value, and the security policy is created with the OVERRIDE NOT AUTHORIZED WRITE SECURITY LABEL option
• Extended indicator variable usage: If enabled, indicator variable values other than 0 (zero) through -7 must not be input (SQLSTATE 22010). Also, if enabled, the default and unassigned extended indicator variable values must not appear in contexts in which they are not supported (SQLSTATE 22539).
• Extended indicator variables: In the assignment-clause of an UPDATE statement, an expression that is a reference to a single host variable, or a host variable being explicitly cast can result in assigning an extended indicator variable value. Assigning an extended indicator variable-based value of unassigned has the effect of leaving the target column set to its current value, as if it had not been specified in the statement. Assigning an extended indicator variable-based value of default assigns the default value of the column. For information about default values of data types, see the description of the DEFAULT clause in the "CREATE TABLE" statement.

  If a target column is not updatable (for example, a column in a view that is defined as an expression), then it must be assigned the extended indicator variable-based value of unassigned (SQLSTATE 42808).

  If the target column is a column defined as GENERATED ALWAYS, then it must be assigned the DEFAULT keyword, or the extended indicator variable-based values of default or unassigned (SQLSTATE 428C9).

  The UPDATE statement must not assign all target columns to an extended indicator variable-based value of unassigned (SQLSTATE 22540).

Notes

• If an update value violates any constraints, or if any other error occurs during the execution of the UPDATE statement, no rows are updated. The order in which multiple rows are updated is undefined.
• An update to a view defined using the WITH ROW MOVEMENT clause could cause a delete operation and an insert operation against the underlying tables of the view. For details, see the description of the CREATE VIEW statement.
• When an UPDATE statement completes execution, the value of SQLERRD(3) in the SQLCA is the number of rows that qualified for the update operation. In the context of an SQL procedure statement, the value can be retrieved using the ROW_COUNT variable of the GET DIAGNOSTICS statement. The SQLERRD(5) field contains the number of rows inserted, deleted, or updated by all activated triggers.
• Unless appropriate locks already exist, one or more exclusive locks are acquired by the execution of a successful UPDATE statement. Until the locks are released, the updated row can only be accessed by the application process that performed the update (except for applications using the Uncommitted Read isolation level). For further information on locking, see the descriptions of the COMMIT, ROLLBACK, and LOCK TABLE statements.
• When updating the column distribution statistics for a typed table, the subtable that first introduced the column must be specified.
• Multiple attribute assignments on the same structured type column occur in the order specified in the SET clause and, within a parenthesized set clause, in left-to-right order.
• An attribute assignment invokes the mutator method for the attribute of the user-defined structured type. For example, the assignment st..a1=x has the same effect as using the mutator method in the assignment st = st..a1(x).
• While a given column may be a target column in only one conventional assignment, a column may be a target column in multiple attribute assignments (but only if it is not also a target column in a conventional assignment).
• When an identity column defined as a distinct type is updated, the entire computation is done in the source type, and the result is cast to the distinct type before the value is actually assigned to the column. (There is no casting of the previous value to the source type before the computation.)
• To have a generated value on a SET statement for an identity column, use the DEFAULT keyword:

     SET NEW.EMPNO = DEFAULT

  In this example, NEW.EMPNO is defined as an identity column, and the value used to update this column is generated.
• For more information about consuming values of a generated sequence for an identity column, or about exceeding the maximum value for an identity column, see INSERT.
• With partitioned tables, an UPDATE WHERE CURRENT OF cursor-name operation can move a row from one data partition to another. After this occurs, the cursor is no longer positioned on the row, and no further UPDATE WHERE CURRENT OF cursor-name modifications to that row are possible. The next row in the cursor can be fetched, however.
• For a column defined using the ROW CHANGE TIMESTAMP clause, the value is always changed on update of the row. If the column is not specified in the SET list explicitly, the database manager still generates a value for that row. The value is unique for each table partition within the database partition and is set to the approximate timestamp corresponding to the row update.
• Extended indicator variables and update triggers: If a target column has been assigned with an extended indicator variable-based value of unassigned, that column is not considered to have been updated. That column is treated as if it had not been specified in the OF column-name list of any update trigger defined on the target table.
• Extended indicator variables and deferred error checks: When extended indicator variables are enabled, validation that would otherwise be done in statement preparation, to recognize an update of a non-updatable column, is deferred until statement execution, except for column level update privilege checking of static UPDATE statements. Whether an error should be reported can be determined only during execution based on the indicator value. The checking of column level update privilege for static UPDATE statements continues to be performed during bind processing even when extended indicator variables are enabled.
• Considerations for a system-period temporal table: The target of the UPDATE statement must not be a fullselect that references a view in the FROM clause followed by a period specification for SYSTEM_TIME if the view is defined with the WITH CHECK OPTION and the view definition includes a WHERE clause containing one of the following syntax elements (SQLSTATE 51046):
  • A subquery that references a system-period temporal table (directly or indirectly)
  • An invocation of an SQL routine that has a package associated with it
  • An invocation of an external routine with a data access indication other than NO SQL
  If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value, an underlying target of the UPDATE statement must not be a system-period temporal table (SQLSTATE 51046), and the target of the UPDATE statement must not be a view defined with the WITH CHECK OPTION if the view definition includes a WHERE clause containing one of the following syntax elements (SQLSTATE 51046):

  • A subquery that references a system-period temporal table (directly or indirectly)
  • An invocation of an SQL routine that has a package associated with it
  • An invocation of an external routine with a data access indication other than NO SQL
  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 database configuration parameter systime_period_adj must be set to Yes for this adjustment in 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.

  The target of an UPDATE statement cannot be a fullselect that references a view in the FROM clause followed by a period specification for SYSTEM_TIME if both of the following conditions are true (SQLSTATE 51046):

  • The view is defined with the WITH CHECK OPTION.
  • The view definition includes a WHERE clause containing one of the following syntax elements:
    • A subquery that references a system-period temporal table (directly or indirectly).
    • An invocation of an SQL routine that has a package associated with it.
    • An invocation of an external routine with a data access indication other than NO SQL.

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

  If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value, 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 one of the following syntax elements (SQLSTATE 51046):

  • A subquery that references a system-period temporal table (directly or indirectly).
  • An invocation of an SQL routine that has a package associated with it.
  • An invocation of an external routine with a data access indication other than NO SQL.
• 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 (SQLSTATE 01695). The database configuration parameter systime_period_adj must be set to Yes for this adjustment in the timestamp value to occur.

  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.

• Considerations for an application-period temporal table: The target of the UPDATE statement must not be a fullselect that references a view in the FROM clause followed by a period specification for BUSINESS_TIME if the view is defined with the WITH CHECK OPTION and the view definition includes a WHERE clause containing one of the following syntax elements (SQLSTATE 51046):
  • A subquery that references an application-period temporal table (directly or indirectly)
  • An invocation of an SQL routine that has a package associated with it
  • An invocation of an external routine with a data access indication other than NO SQL
  If the CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null value, the target of the UPDATE statement must not be a view defined with the WITH CHECK option if the view definition includes a WHERE clause containing one of the following syntax elements (SQLSTATE 51046):

  • A subquery that references an application-period temporal table (directly or indirectly)
  • An invocation of an SQL routine that has a package associated with it
  • An invocation of an external routine with a data access indication other than NO SQL

  An UPDATE statement for an application-period temporal table that contains a FOR PORTION OF BUSINESS_TIME clause indicates between which two points in time that the specified updates are effective. When FOR PORTION OF BUSINESS_TIME is specified and the period value for a row, specified by the values of the row-begin column and row-end column, is only partially contained in the period specified from value1 up to value2, the row is updated and one or two rows are automatically inserted to represent the portion of the row that is not changed. New values are generated for each generated column in an application-period temporal table for each row that is automatically inserted as a result of an update operation on the table. If a generated column is defined as part of a unique or primary key, parent key in a referential constraint, or unique index, it is possible that an automatic insert will violate a constraint or index in which case an error is returned.

  When a row is inserted into an application-period temporal table that has either a primary key or unique constraint with the BUSINESS_TIME WITHOUT OVERLAPS clause defined, or a unique index with the BUSINESS_TIME WITHOUT OVERLAPS clause defined, if the period defined by the begin and end columns of the BUSINESS_TIME period overlap the period defined by the begin and end columns of the BUSINESS_TIME period for another row with the same unique constraint or unique index in the table, an error is returned.

  The target of an UPDATE statement cannot be a fullselect that references a view in the FROM clause followed by a period specification for BUSINESS_TIME if both of the following conditions are true (SQLSTATE 51046):

  • The view is defined with the WITH CHECK OPTION.
  • The view definition includes a WHERE clause containing one of the following syntax elements:
    • A subquery that references an application-period temporal table (directly or indirectly).
    • An invocation of an SQL routine that has a package associated with it.
    • An invocation of an external routine with a data access indication other than NO SQL.
  If the CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null value, 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 one of the following syntax elements (SQLSTATE 51046):

  • A subquery that references an application-period temporal table (directly or indirectly).
  • An invocation of an SQL routine that has a package associated with it.
  • An invocation of an external routine with a data access indication other than NO SQL.
  When an application-period temporal table is the target of an UPDATE statement, the value in effect for the CURRENT TEMPORAL BUSINESS_TIME special register is not the null value, and the BUSTIMESENSITIVE bind option is set to YES, the following additional predicates are implicit:

        bt_begin <= CURRENT TEMPORAL BUSINESS_TIME
    AND bt_end > CURRENT TEMPORAL BUSINESS_TIME
  

  where bt_begin and bt_end are the begin and end columns of the BUSINESS_TIME period of the target table of the UPDATE statement.
• Considerations for application-period temporal tables and triggers: When a row is updated and the FOR PORTION OF BUSINESS_TIME clause is specified, additional rows may be implicitly inserted to reflect any portion of the row that was not updated. Any existing update triggers are activated for the rows updated, and any existing insert triggers are activated for rows that are implicitly inserted.

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 the null value 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'

  This statement could also be written as follows.

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

• Example 4: Update the salary and the commission column of the employee with employee number 000120 to the average of the salary and of the commission of the employees of the updated row's department, respectively.

     UPDATE (SELECT EMPNO, SALARY, COMM,
       AVG(SALARY) OVER (PARTITION BY WORKDEPT),
       AVG(COMM) OVER (PARTITION BY WORKDEPT)
       FROM EMPLOYEE E) AS E(EMPNO, SALARY, COMM, AVGSAL, AVGCOMM)
     SET (SALARY, COMM) = (AVGSAL, AVGCOMM)
     WHERE EMPNO = '000120'

  The previous statement is semantically equivalent to the following statement, but requires only one access to the EMPLOYEE table, whereas the following statement specifies the EMPLOYEE table twice.

     UPDATE EMPLOYEE EU
       SET (EU.SALARY, EU.COMM)
       =
     (SELECT AVG(ES.SALARY), AVG(ES.COMM)
       FROM EMPLOYEE ES
       WHERE ES.WORKDEPT = EU.WORKDEPT)
       WHERE EU.EMPNO = '000120'

• Example 5:  In a C program display the rows from the EMPLOYEE table and then, if requested to do so, change the job (JOB) of certain employees to the new job keyed in.

     EXEC SQL  DECLARE C1 CURSOR FOR
                    SELECT *
                      FROM EMPLOYEE
                      FOR UPDATE OF JOB;
  
     EXEC SQL  OPEN C1;
  
     EXEC SQL  FETCH C1 INTO ...     ;
     if ( strcmp (change, "YES") == 0 )
       EXEC SQL  UPDATE EMPLOYEE
                   SET JOB = :newjob
                   WHERE CURRENT OF C1;
  
     EXEC SQL  CLOSE C1;

• Example 6:  These examples mutate attributes of column objects.
  Assume that the following types and tables exist:

     CREATE TYPE POINT AS (X INTEGER, Y INTEGER)
       NOT FINAL WITHOUT COMPARISONS
       MODE DB2SQL

     CREATE TYPE CIRCLE AS (RADIUS INTEGER, CENTER POINT)
       NOT FINAL WITHOUT COMPARISONS
       MODE DB2SQL

     CREATE TABLE CIRCLES (ID INTEGER, OWNER VARCHAR(50), C CIRCLE

  The following example updates the CIRCLES table by changing the OWNER column and the RADIUS attribute of the CIRCLE column where the ID is 999:

     UPDATE CIRCLES
       SET OWNER = 'Bruce'
         C..RADIUS = 5
       WHERE ID = 999

  The following example transposes the X and Y coordinates of the center of the circle identified by 999:

     UPDATE CIRCLES
       SET C..CENTER..X = C..CENTER..Y,
         C..CENTER..Y = C..CENTER..X
       WHERE ID = 999

  The following example is another way of writing both of the previous statements. This example combines the effects of both of the previous examples:

     UPDATE CIRCLES
       SET (OWNER,C..RADIUS,C..CENTER..X,C..CENTER..Y) =
         ('Bruce',5,C..CENTER..Y,C..CENTER..X)
       WHERE ID = 999

• Example 7: Update the XMLDOC column of the DOCUMENTS table with DOCID '001' to the character string that is selected and parsed from the XMLTEXT table.

     UPDATE DOCUMENTS SET XMLDOC =
       (SELECT XMLPARSE(DOCUMENT C1 STRIP WHITESPACE)
         FROM XMLTEXT WHERE TEXTID = '001')
     WHERE DOCID = '001'

• Example 8: A new location column has been added to the project table. Update the project location with the location of the department handling the project.

     UPDATE PROJECT P
        SET P.LOCATION = D.LOCATION
        FROM DEPARTMENT D
        WHERE P.DEPTNO = D.DEPTNO;

• Example 9: Update the estimated project staffing to the max staffing required for all activities within the project.

     UPDATE PROJECT P
        SET P.PRSTAFF = S.ACSTAFF
        FROM (SELECT PROJNO, MAX(ACSTAFF) ACSTAFF FROM PROJACT GROUP BY PROJNO) S
        WHERE P.PROJNO = S.PROJNO AND
              P.PROJNAME = 'PAYROLL PROGRAMMING';

• Example 10: Update an employee's work department to the project department he is assigned to.

     UPDATE EMPLOYEE E
        SET E.WORKDEPT = P.DEPTNO
        FROM PROJECT P JOIN EMPPROJACT EP ON P.PROJNO = EP.PROJNO
        WHERE E.EMPNO = EP.EMPNO AND
              E.FIRSTNME = 'PHILIP' AND E.LASTNAME = 'SMITH';