UPDATE

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.

Invocation

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

Authorization

Authority requirements depend on whether the object identified in the statement is a user-defined table, a catalog table for which updates are allowed, or a view, and whether SQL standard rules are in effect:

When a user-defined table is identified: The privilege set must include at least one of the following:

• DATAACCESS authority
• The UPDATE privilege on the table
• The UPDATE privilege on each column to be updated
• Ownership of the table
• DBADM authority on the database that contains the table
• SYSADM authority

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

When a catalog table is identified: The privilege set must include at least one of the following:

• ACCESSCTRL authority
• DATAACCESS authority
• The UPDATE privilege on each column to be updated
• DBADM authority on the catalog database
• SYSCTRL authority
• SYSADM authority
• SYSADM authority
• System DBADM authority

When a view is identified: The privilege set must include at least one of the following:

• DATAACCESS authority
• SYSADM authority
• UPDATE privilege on the view
• UPDATE privilege on each column to be updated

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, the privilege set must include at least one of the following:

• The SELECT privilege on the table or view
• Ownership of the table or view
• DBADM authority on the database that contains the table, if the target is a table and that table that is not a catalog table
• DATAACCESS
• SYSADM authority

When FOR PORTION OF BUSINESS_TIME is specified: The privilege set must include at least one of the following:

• The UPDATE privilege on the columns of the BUSINESS_TIME period
• The UPDATE privilege on the table
• Ownership of the table or view
• DBADM authority on the database that contains the table, if the target is a table and that table that is not a catalog table
• DATAACCESS
• SYSADM authority

If the search-condition in a searched UPDATE includes a subquery, or if the assignment-clause includes a scalar-fullselect or a row-fullselect, see Authorization for an explanation of the authorization required.

The owner of a view, unlike the owner of a table, might not have UPDATE authority on the view (or might have UPDATE authority without being able to grant it to others). The nature of the view itself can preclude its use for UPDATE. For more information, see the discussion of authority in CREATE VIEW.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the statement is dynamically prepared, the privilege set is determined by the DYNAMICRULES behavior in effect (run, bind, define, or invoke) and is summarized in Table 1. (For more information on these behaviors, including a list of the DYNAMICRULES bind option values that determine them, see Authorization IDs and dynamic SQL).

Description

table-name or view-name

Identifies the object of the UPDATE statement. The name must identify a table or view that exists at the DB2 subsystem that is identified by the implicitly or explicitly specified location name. The name must not identify one of the following tables:

• An auxiliary table
• A created temporary table or a view of a created temporary table
• A catalog table with no updatable columns or a view of a catalog table with no updatable columns
• A directory table
• A read-only view that has no INSTEAD OF trigger defined for its update operations. (For a description of a read-only view, see CREATE VIEW.)
• A system-maintained materialized query table
• A table that is implicitly created for an XML column

In an IMS™ or CICS® application, the DB2 subsystem that contains the identified table or view must be a remote server that supports two-phase commit.

A catalog table or a view of a catalog table can be identified if every column identified in the SET clause is an updatable column. If a column of a catalog table is updatable, its description in DB2 catalog tables indicates that the column can be updated. If the object table is SYSIBM.SYSSTRINGS, any column other than IBMREQD can be updated, but the rows that are selected for update must be rows that are provided by the user (the value of the IBMREQD column is N) and only certain values can be specified as explained in How an entry in SYSIBM.SYSSTRINGS works with character conversion.

period-clause

Specifies that a period clause applies to the target of the update operation. The same period name must not be specified more than one time. The object of the UPDATE statement must not be a view.

FOR PORTION OF BUSINESS_TIME

Specifies that the update only applies to row values for the portion of the BUSINESS_TIME period in the row that is specified by the period clause. BUSINESS_TIME must be a period that is defined on the table.

FOR PORTION OF BUSINESS_TIME must not be specified if the value of the CURRENT TEMPORAL BUSINESS_TIME special register is not NULL when the BUSTIMESENSITIVE bind option is set to YES.

FROM value1 TO value2

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

For the period condition that is specified with FROM value1 TO value2, the period that is specified with period-name in a row of the target update:

• 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 is greater than or equal to value1 and the value for the end column is less than or equal to value2.
• Is not contained in the period if both columns of period-name are less than value1 or greater than or equal to value2.
• Is partially contained in the specified period if the period in 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 both the beginning and the end of the specified period.

If the period, period-name in a row is not contained in the specified period, the row is not updated. Otherwise, the update is applied based on the specification of PORTION OF and how the values in the columns of period-name overlap the specified period as follows:

• If the period, period-name 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 period-name are unchanged.
• If the period, period-name 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.
  • An additional row is inserted using the original values from the row, except that the end column is set to value1, and new values are used for other generated columns.
• If the period, period-name 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.
  • An additional row is inserted using the original values from the row, except that the begin column is set to value2, and new values are used for other generated columns.
• If the period, period-name 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.
  • An additional row is inserted using the original values from the row, except that the end column is set to value1, and new values are used for other generated columns.
  • An additional row is inserted using the original values from the row, except that the begin column is set to value2, and new values are used for other generated columns.

Any existing update triggers are activated for the updated rows and any existing insert triggers are activated for rows that are implicitly inserted.

value1, value2

Specifies expressions that return a value of a built-in data type. The result of each expression must be comparable to the data type of the columns of the specified period. See the comparison rules described in Assignment and comparison. Each expression can contain any of the following supported operands:

• A constant
• A special register
• A variable (host variable, SQL variable, SQL parameter, or transition variable)
• A built-in scalar function whose arguments are supported operands
• A CAST specification where the cast operand is a supported operand
• An expression that uses arithmetic operators and operands

Each expression must not have a timestamp precision that is greater than the precision of the columns for the period.

If the begin and end columns of the period are defined as TIMESTAMP WITHOUT TIME ZONE, each expression must not return a value of a timestamp with a time zone.

correlation-name

Can be used within search-condition or assignment-clause to designate the table or view. (For an explanation of correlation-name, see Correlation names.)

include-column

Specifies a set of columns that are included, along with the columns of table-name or view-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 or view-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 or view that is specified in table-name or view-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. See CREATE TABLE for a description of each built-in type.

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, and NULL and DEFAULT keywords 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 or view, and that column must be updatable if extended indicator variables are not enabled. The column must not identify a generated column or a view 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.

A column that is defined as part of a BUSINESS_TIME period must not be specified if the UPDATE statement contains a period-clause.

Assignments to included columns are only processed when the UPDATE statement is nested in the FROM clause of a SELECT statement or a SELECT INTO statement. There must be at least one assignment clause that specifies a column-name that is not an INCLUDE column. The null value is returned for an included column that is not set by using an explicit SET clause.

For a positioned update, allowable column names can be further restricted to those in a certain list. This list appears in the FOR UPDATE clause of the SELECT statement for the associated cursor. The clause can be omitted by using the conditions that are described in Positioned updates of columns.

A view column that is 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.

expression

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

If expression is a host variable, the host variable can include an indicator variable. When extended indicator variables are enabled, an expression must not be more complex than a reference to a single host variable if the indicator is set to an extended indicator value of default (-5) or unassigned (-7). In addition, a CAST specification can be used if either of the following is true:

• The target column is defined as nullable.
• the target column is defined as NOT NULL with a non-null default, the source of the CAST specification is a single host variable, and the data attributes (data type, length, precision, and scale) of the host variable are the same as the result of the cast specification.

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

DEFAULT

Specifies that the default value is used based on how the corresponding column is defined in the table. The value that is assigned depends on how the column is defined.

• If the column is defined using the IDENTITY clause, the column is generated by the DB2 system.
• If the column is defined as a row change timestamp column, the column value is generated by the DB2 system.
• If the column is defined using the WITH DEFAULT clause, the value is set to the default that is defined for the column.
• If the column is defined without specifying the WITH DEFAULT clause, the GENERATED clause, or the NOT NULL clause, the value is NULL.
• If the column is specified in the INCLUDE column list, the column value is set to null.

A ROWID column must not be set to the DEFAULT keyword.

An identity column or a row change timestamp column that is defined as GENERATED ALWAYS can be set only to the DEFAULT keyword.

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

NULL

Specifies the null value as the new value of the column. Specify NULL only for nullable columns.

row-fullselect

Specifies a fullselect that returns a single row. The column values are assigned to each of the corresponding column-names. 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.

For a positioned update, if the table or view that is the object of the UPDATE statement is used in the fullselect, a column from the instance of the table or view in the fullselect cannot be the same as column-name, a column being updated.

If the fullselect refers to columns to be updated, the value of such a column in the fullselect is the value of the column in the row before the row is updated.

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

The search-condition is applied to each row of the table or view 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 or view 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.

A positioned UPDATE must not be specified for a cursor that references a view on which an instead of update trigger is defined, even if the view is an updatable view.

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 of 1 to 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.

isolation-clause

Specifies the isolation level used when locating the rows to be updated by the statement.

WITH

Introduces the isolation level, which may be one of the following:

RR

Repeatable read

RS

Read stability

CS

Cursor stability

The default isolation level of the statement is the isolation level of the package or plan in which the statement is bound, with the package isolation taking precedence over the plan isolation. When a package isolation is not specified, the plan isolation is the default.

SKIP LOCKED DATA

Specifies that rows are skipped when incompatible locks are held on the row by other transactions. These rows can belong to any accessed table that is specified in the statement. SKIP LOCKED DATA can be used only when isolation CS or RS is in effect and applies only to row level or page level locks.

SKIP LOCKED DATA can be specified only in the searched UPDATE statement (or the searched update operation of a MERGE statement). SKIP LOCKED DATA is ignored if it is specified when the isolation level that is in effect is repeatable read (WITH RR) or uncommitted read (WITH UR). The default isolation level of the statement depends on the isolation level of the package or plan with which the statement is bound, with the package isolation taking precedence over the plan isolation. When a package isolation is not specified, the plan isolation is the default.

QUERYNO integer

Specifies the number to be used for this SQL statement in EXPLAIN output and trace records. The number is used for the QUERYNO column of the plan table for the rows that contain information about this SQL statement. This number is also used in the QUERYNO column of the SYSIBM.SYSSTMT and SYSIBM.SYSPACKSTMT catalog tables.

If the clause is omitted, the number associated with the SQL statement is the statement number assigned during precompilation. Thus, if the application program is changed and then precompiled, that statement number might change.

Using the QUERYNO clause to assign unique numbers to the SQL statements in a program is helpful:

• For simplifying the use of optimization hints for access path selection
• For correlating SQL statement text with EXPLAIN output in the plan table

For information on using optimization hints, such as enabling the system for optimization hints and setting valid hint values, and for information on accessing the plan table, see Managing DB2 Performance.

Notes

Update rules:

Update values must satisfy the following rules. If they do not, or if other errors occur during the execution of the UPDATE statement, no rows are updated and the position of the cursors are not changed.

• Assignment. Update values are assigned to columns using the assignment rules described in Language elements.
• 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 and expressions that contain a scalar-fullselect must return no more than one row.
  • Unique constraints and unique indexes: If the identified table (or base table of the identified view) has any unique indexes or unique constraints, each row that is updated in the table must conform to the limitations that are imposed by those indexes and constraints.

    All uniqueness checks are effectively made at the end of the statement. In the case of a multi-row update, this validation occurs after all the rows are updated.

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

    All checks constraints are effectively validated at the end of the statement. In the case of a multi-row update, this validation occurs after all the rows are updated.

  • Views and the WITH CHECK OPTION. For views defined with WITH CHECK OPTION, an updated row must conform to the definition of the view. If the view you name is dependent on other views whose definitions include WITH CHECK OPTION, the updated rows must also conform to the definitions of those views. For an explanation of the rules governing this situation, see CREATE VIEW.

    For views that are not defined with WITH CHECK OPTION, you can change the rows 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.

  • Field and validation procedures. The updated rows must conform to any constraints imposed by any field or validation procedures on the identified table (or on the base table of the identified view).
• Referential constraints. The value of the parent key in a parent row must not be changed. If the update value produces 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.

  All referential constraints are effectively checked at the end of the statement. In the case of a multi-row update, this validation occurs after all the rows are updated.

• Indexes with VARBINARY columns. If the identified table has an index on a VARBINARY column or a column that is a distinct type that is based on VARBINARY data type, that index column cannot specify the DESC attribute. To use the SQL data change operation on the identified table, either drop the index or alter the data type of the column to BINARY and then rebuild the index.
• Triggers. An UPDATE statement might cause triggers to activate. A trigger might cause other statements to be executed or raise error conditions based on the update values. If an UPDATE statement for a view causes an instead of trigger to activate, validity, referential integrity, and check constraints are checked against the data changes that are performed in the trigger and not against the view that causes the trigger to activate or its underlying base tables.

Number of rows updated:

Normally, after an UPDATE statement completes execution, the value of SQLERRD(3) in the SQLCA is the number of rows updated. (For a complete description of the SQLCA, including exceptions to the preceding sentence, see SQL communication area (SQLCA).)

Nesting user-defined functions or stored procedures:

An UPDATE statement can implicitly or explicitly refer to user-defined functions or stored procedures. This is known as nesting of SQL statements. A user-defined function or stored procedure that is nested within the UPDATE must not access the table being updated.

Locking:

Unless appropriate locks already exist, one or more exclusive locks are acquired by the execution of a successful update operation. Until a commit or rollback operation releases the locks, only the application process that performed the insert can access the updated row. If LOBs are not updated, application processes that are running with uncommitted read can also access the updated row. The locks can also prevent other application processes from performing operations on the table. However, application processes that are running with uncommitted read can access locked pages and rows.

Locks are not acquired on declared temporary tables.

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.

Rules for positioned UPDATE with a SENSITIVE STATIC scrollable cursor:

When a SENSITIVE STATIC scrollable cursor has been declared, the following rules apply:

• Update attempt of delete holes. If, with a positioned update against a SENSITIVE STATIC scrollable cursor, an attempt is made to update a row that has been identified as a delete hole, an error occurs.
• Update operations. Positioned update operations with SENSITIVE STATIC scrollable cursors perform as follows:
  1. The SELECT list items in the target row of the base table of the cursor are compared with the values in the corresponding row of the result table (that is, the result table must still agree with the base table). If the values are not identical, then the update operation is rejected, and an error occurs. The operation may be attempted again after a successful FETCH SENSITIVE has occurred for the target row.
  2. The WHERE clause of the SELECT statement is re-evaluated to determine whether the current values in the base table still satisfy the search criteria. The values in the SELECT list are compared to determine that these values have not changed. If the WHERE clause evaluates as true, and the values in the SELECT have not changed, the update operation is allowed to proceed. Otherwise, the update operation is rejected, an error occurs, and an update hole appears in the cursor.
• Update of update holes. Update holes are not permanent. It is possible for another process, or a searched update in the same process, to update an update hole row so that it is no longer an update hole. Update holes become visible with a FETCH SENSITIVE for positioned updates and positioned deletes.
• Result table. After the base table is updated, the row is re-evaluated and updated in the temporary result table. At this time, it is possible that the positioned update changed the data such that the row does not qualify the search condition, in which case the row is marked as an update hole for subsequent FETCH operations.

Updating rows in a table with multilevel security:

When you update rows in a table with multilevel security, DB2 compares the security label of the user (the primary authorization ID) to the security label of the row. The update proceeds according to the following rules:

• If the security label of the user and the security label of the row are equivalent, the row is updated and the value of the security label is determined by whether the user has write-down privilege:
  • If the user has write-down privilege or write-down control is not enabled, the user can set the security label of the row to any valid security label. The value that is specified for the security label column must be assignable to a column that is defined as CHAR(8) FOR SBCS DATA NOT NULL.
  • If the user does not have write-down privilege and write-down control is enabled, the security label of the row is set to the value of the security label of the user.
• If the security label of the user dominates the security label of the row, the result of the UPDATE statement is determined by whether the user has write-down privilege:
  • If the user has write-down privilege or write-down control is not enabled, the row is updated and the user can set the security label of the row to any valid security label.
  • If the user does not have write-down privilege and write-down control is enabled, the row is not updated.
• If the security label of the row dominates the security label of the user, the row is not updated.

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 or role of the process. 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 or role 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, the accessible rows are the rows to be updated.

  Column masks are not applicable in this step.

  If the table is not enforced by row access control, the WHERE clause determines the rows to be updated, otherwise all rows in the table are to be updated.

• If there are rows to be updated, the following rules determine whether those rows can be updated:
  • For every column to be updated, the new value of the column must not be affected by enabled column masks whose columns are referenced when deriving the new value.

    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 is also column access control activated, the column mask applied to derive the new values must ensure the evaluation of the access control rules defined in the column mask resolves the column to itself, not to 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 at run time.

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

The above rules are not applicable to the included columns. The included columns are subject to the rules for the select list because they are not the columns of the object table of the UPDATE statement.

Extended indicator variable usage:

When extended indicator variables are enabled, negative indicator values that are outside the range of -1 through -7 must not be specified, and the default and unassigned extended indicator values must not appear in contexts in which they are not supported.

Extended indicator variables:

Assigning an extended indicator 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 value of default assigns the default value to the column.

The UPDATE statement must not assign all target columns to the extended indicator value of unassigned.

Extended indicator variables and update triggers:

If a target column has been assigned an extended indicator value of unassigned, the column is not considered to have been updated. The column is treated as if it had not been specified in the OF column-name list of any update trigger that is defined on the target table.

Considerations for a generated column:

A generated column that is defined as GENERATED ALWAYS should not be specified as the target of an assignment clause unless the value that is to be assigned is specified with the DEFAULT keyword or an extended indicator that specifies that a default value is to be assigned.

Considerations for a system-period temporal table:

When a row of a system-period temporal table is updated, DB2 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 by using the time-of-day clock during the execution of the first data change statement in the transaction that requires a value to be assigned to a row-begin column or transaction-start-ID column in a table. This also occurs when a row in a system-period temporal table is deleted. DB2 ensures the uniqueness of the generated values for a row-begin column across transactions. If multiple rows are updated within a single SQL transaction, the values for the row-begin column are the same for all the rows and are unique from the values that are generated for the column for another transaction.
• A transaction-start-ID column is assigned a unique timestamp value per unit of work or the null value. The null value is assigned to the transaction-start-ID column if the column is nullable. Otherwise, the value is generated by using the time-of-day clock during execution of the first data change statement in the unit of work that requires a value to be assigned to a row-begin column or transaction-start-ID column in a table. This also occurs when a row in a system-period temporal table is deleted. If multiple rows are updated within a single SQL unit of work, the values for the transaction-start-ID column are the same for all the rows and are unique from the values that are generated for the column for another unit of work.

If the UPDATE statement has a search condition that contains a correlated subquery that references historical rows (explicitly referencing the name of the history table or implicitly referenced 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) are potentially visible to update operations for the rows that are subsequently processed for the statement.

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. DB2 generates the value by using the time-of-day clock during the execution of the first data change statement in the transaction that requires a value to be assigned to a row-begin or transaction-start-ID column in a table. This also occurs when a row in a system-period temporal table is deleted. DB2 ensures uniqueness of the generated values for an end column in a history table across transactions. If a conflicting transaction is updating the same row in the system-period temporal table and the row that is to be inserted into the associated history table would have an end timestamp value that is greater than the begin timestamp value, an error is returned.

Considerations for an application-period temporal table:

An UPDATE statement that contains a FOR PORTION OF BUSINESS_TIME clause for an application-period temporal table indicates the two points in time between which the specified updates are effective.

Suppose that FOR PORTION OF BUSINESS_TIME is specified, and the period value for a row is only partially contained in the period that is specified from value1 up to value2. (The period value for a row is specified by the values of the begin column and end column for the BUSINESS_TIME period.) In this case, the row is updated and one or two rows are automatically inserted to represent the portion of the row that is not changed. For each row that is automatically inserted as a result of an update operation on the table, new values are generated for each generated column in the application-period temporal table. If a generated column is defined as part of a unique or primary key, parent key in a referential constraint, or unique index, an automatic insert might violate a constraint or index. In this case, an error is returned.

Other SQL statements in the same unit of work:

The following statements cannot follow an UPDATE statement in the same unit of work:

• An ALTER TABLE statement that changes the data type of a column (ALTER COLUMN SET DATA TYPE)
• An ALTER INDEX statement that changes the padding attribute of an index with varying-length columns (PADDED to NOT PADDED or vice versa)
• A CREATE TABLE statement that creates an accelerator-only table.
• An INSERT, UPDATE, or DELETE statement that updates accelerator-only tables from a different accelerator.

Using UPDATE to reset AREO* status on a table:

An UPDATE statement will reset the AREO* state of a table if all conditions are true:

• The statement is a searched UPDATE statement. An UPDATE statement within a SELECT statement will not reset the AREO* state.
• The expression in the SET clause is not a scalar-fullselect or row-fullselect
• The update operation is against a table in a universal table space
• The table does not have row access control activated
• The SKIP LOCKED DATA clause is not specified
• The WHERE clause is not specified
• A resource unavailable condition is not encountered.

  No error or warning SQLCODE is returned if a resource unavailable condition is encountered. Only a resource unavailable console message will be displayed.

A DISPLAY DATABASE command can be used to determine if AREO* is reset.