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 for this view. If such a trigger is defined, the trigger is activated instead.

The table or view can exist at the current server or at any Db2 subsystem with which the current server can establish a connection.

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 Dynamic preparation and execution. (For more information on these behaviors, including a list of the DYNAMICRULES bind option values that determine them, see Authorization IDs and dynamic SQL).

searched update:

Read syntax diagramSkip visual syntax diagram UPDATE table-nameview-name period-clausecorrelation-nameinclude-column SET assignment-clause WHEREsearch-condition1isolation-clauseSKIP LOCKED DATAQUERYNOinteger
Notes:
  • 1 The same clause must not be specified more than one time.

positioned update:

Read syntax diagramSkip visual syntax diagram UPDATE table-nameview-name correlation-name SET assignment-clause WHERE CURRENT OF cursor-name FOR ROWhost-variableinteger-constantOF ROWSET

period-clause:

Read syntax diagramSkip visual syntax diagram FOR PORTION OF BUSINESS_TIME FROMvalue1TOvalue2

include-column:

Read syntax diagramSkip visual syntax diagram INCLUDE ( ,column-namedata-type )

data-type:

Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type

built-in-type:

Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERIC(5,0)( integer,0, integer)FLOAT(53)( integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)( integer)CHARACTERCHARVARYINGVARCHAR( integer)FOR BIT DATAGRAPHIC(1)( integer)VARGRAPHIC( integer)BINARY(1)( integer)BINARY VARYINGVARBINARY( integer)DATETIMETIMESTAMP(6)(integer)WITHOUT TIME ZONEWITH TIME ZONE

assignment clause:

Read syntax diagramSkip visual syntax diagram,column-name=expressionDEFAULTNULL(,column-name)=(,1expressionDEFAULTNULLrow-fullselect2UNPACK-function-invocation3)
Notes:
  • 1 The number of expressions, DEFAULT, and NULL keywords must match the number of column-names. Expressions must not refer to UNPACK-function-invocation..
  • 2 The number of columns in the select list must match the number of column-names.
  • 3 The number of items returned from UNPACK-function-invocation must match the number of column names.

isolation-clause:

Read syntax diagramSkip visual syntax diagram WITH RRRSCS

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
  • Start of changeA directory tableEnd of change
  • 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
  • Start of changeAn archive-enabled table if any of the following conditions are true:
    • The SYSIBMADM.MOVE_TO_ARCHIVE global variable is set to Y.
    • The SYSIBMADM.GET_ARCHIVE global variable is set to Y, the ARCHIVESENSITIVE bind option is set to YES, and the operation is a positioned update.
    End of change

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. Start of changeIf the target of the update operation is a view:End of changeStart of change
  • The FROM clause of the outer fullselect of the view definition must include a reference, directly or indirectly, to an application-period temporal table.
  • The result table of the outer fullselect of the view definition must include, explicitly or implicitly, the start and end columns of the BUSINESS_TIME period.
  • An INSTEAD OF trigger must not be defined for the view.
End of change
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.
    • Start of changeAn 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.End of change
  • 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.
    • Start of changeAn 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.End of change
  • 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.
    • Start of changeAn additional row is inserted using the original values from the row, except that the end column is set to value1, a column defined as DATA CHANGE OPERATION is set to 'I', and new values are used for other generated columns.End of change
    • Start of changeAn additional row is inserted using the original values from the row, except that the begin column is set to value2, a column defined as DATA CHANGE OPERATION is set to 'I', and new values are used for other generated columns.End of change

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
  • Start of changeA variableEnd of change
  • Start of changeAn array element specificationEnd of change
  • 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.

Start of changeA period clause for a view must not contain a global variable or an untyped parameter marker.End of change

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.

Start of changeThe CCSID 1208 and CCSID 1200 clauses must not be specified for an INCLUDE column.End of change

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
Start of changeIdentifies a column that is to be updated. 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 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 name must not be specified more than once.

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.

End of change
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.

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.

Start of changeIf 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.End of change

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.
DEFAULT
Specifies that the default value is used based on how the corresponding column is defined in the table. Start of changeDEFAULT must not be specified for a ROWID, row-begin, row-end, transaction-start-ID, or generated expression column.End of change The value that is assigned depends on how the column is defined.
  • Start of changeIf the column is a generated expression, the column value will be generated by the Db2 subsystem based on the result of the expression.End of change
  • Start of changeIf the column is an identity column, row change timestamp column, row-begin column, row-end column, or transaction-start-ID column, the Db2 subsystem will generate a new value.End of change
  • 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.

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

Start of change DEFAULT must be specified for a column that was defined as GENERATED ALWAYS. A valid value can be specified for a column that was defined as GENERATED BY DEFAULT.End of change

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.

Start of changeUNPACK-function-invocationEnd of change
Start of changeSpecifies an invocation of the UNPACK built-in function. The number of fields that are returned by the UNPACK function invocation must be the same as the number of column-names.End of change
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 1 - 32767.

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

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

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

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 more about enabling and using optimization hints, see Influencing access path selection

For information on accessing the plan table, see Investigating SQL performance by using EXPLAIN.

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.

Start of changeExtended indicators usage:End of change
Start of changeWhen 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 where they are not supported.End of change
Start of changeExtended indicators:End of change
Start of changeSpecifying an indicator value with the extended indicator value of UNASSIGNED has the same effect as if the column had not been specified in the statement. 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, such as an identity column that is defined as GENERATED ALWAYS, it must be assigned the extended indicator value of UNASSIGNED.

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

End of change
Start of changeExtended indicators and update triggers:End of change
Start of changeIf the indicator value for a target column is 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 that is defined on the target table or view.End of change
Start of changeExtended indicators and deferred error checks:End of change
Start of changeWhen extended indicators are enabled, validation that would normally be done during statement preparation to recognize an insert into a non-updatable column is deferred until the statement is executed.End of change
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.

If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value, the underlying target of the UPDATE statement cannot be a system-period temporal table. This restriction applies regardless of whether the system-period temporal table is directly or indirectly referenced.

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.

When an application-period table is the target of an UPDATE statement and the value in effect for the CURRENT TEMPORAL BUSINESS_TIME special register is not the null value, Db2 adds the following additional predicates to the statement:

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

In the preceding code, bt_begin and bt_end are the begin and end columns of the BUSINESS_TIME period of the target table of the UPDATE statement.

Start of changeArchive-enabled tables:End of change
Start of changeA reference to an archive-enabled table as the target of the UPDATE statement does not affect rows in the associated archive table.

A data change statement must not reference an archive-enabled table when a system-period temporal table or application-period temporal table is also referenced.

End of change
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.

Examples

Example 1
Change employee 000190's telephone number to 3565 in DSN8B10.EMP.
   UPDATE DSN8B10.EMP
     SET PHONENO='3565'
     WHERE EMPNO='000190';
Example 2
Give each member of department D11 a 100-dollar raise.
  UPDATE DSN8B10.EMP
    SET SALARY = SALARY + 100
    WHERE WORKDEPT = 'D11';
Example 3
Employee 000250 is going on a leave of absence. Set the employee's pay values (SALARY, BONUS, and COMMISSION) to null.
   UPDATE DSN8B10.EMP
     SET SALARY = NULL, BONUS = NULL, COMM = NULL
     WHERE EMPNO='000250';
Alternatively, the statement could also be written as follows:
   UPDATE DSN8B10.EMP
     SET (SALARY, BONUS, COMM) = (NULL, NULL, NULL)
     WHERE EMPNO='000250';
Example 4
Assume that a column named PROJSIZE has been added to DSN8B10.EMP. The column records the number of projects for which the employee's department has responsibility. For each employee in department E21, update PROJSIZE with the number of projects for which the department is responsible.
   UPDATE DSN8B10.EMP
     SET PROJSIZE = (SELECT COUNT(*)
                     FROM DSN8B10.PROJ
                     WHERE DEPTNO = 'E21') 
     WHERE WORKDEPT = 'E21';               
Example 5
Double the salary of the employee represented by the row on which the cursor C1 is positioned.
   EXEC SQL UPDATE DSN8B10.EMP
     SET SALARY = 2 * SALARY
     WHERE CURRENT OF C1;
Example 6
Assume that employee table EMP1 was created with the following statement:
   CREATE TABLE EMP1
     (EMP_ROWID    ROWID GENERATED ALWAYS,
      EMPNO        CHAR(6),
      NAME         CHAR(30),
      SALARY       DECIMAL(9,2),
      PICTURE      BLOB(250K),
      RESUME       CLOB(32K));
 
Assume that host variable HV_EMP_ROWID contains the value of the ROWID column for employee with employee number '350000'. Using that ROWID value to identify the employee and user-defined function UPDATE_RESUME, increase the employee's salary by $1000 and update that employee's resume.
   EXEC SQL UPDATE EMP1
     SET SALARY = SALARY + 1000,
         RESUME = UPDATE_RESUME(:HV_RESUME)
     WHERE EMP_ROWID = :HV_EMP_ROWID;
Example 7
In employee table X, give each employee whose salary is below average a salary increase of 10%.
  EXEC SQL UPDATE EMP X
    SET SALARY = 1.10 * SALARY
    WHERE SALARY < (SELECT AVG(SALARY) FROM EMP Y
    WHERE X.JOBCODE = Y.JOBCODE);
Example 8
Raise the salary of the employees in department 'E11' whose salary is below average to the average salary.
  EXEC SQL UPDATE EMP T1
    SET SALARY = (SELECT AVG(T2.SALARY) FROM EMP T2)
    WHERE WORKDEPT = 'E11' AND
          SALARY < (SELECT AVG(T3.SALARY) FROM EMP T3);  
Example 9
Give the employees in department 'E11' a bonus equal to 10% of their salary.
  EXEC SQL 
    DECLARE C1 CURSOR FOR  
      SELECT BONUS  
      FROM DSN8710.EMP 
      WHERE WORKDEPT = 'E12'  
      FOR UPDATE OF BONUS;  
  EXEC SQL  
    UPDATE DSN8710.EMP  
      SET BONUS = ( SELECT .10 * SALARY FROM DSN8710.EMP Y  
                    WHERE EMPNO = Y.EMPNO ) 
      WHERE CURRENT OF C1;
Example 10
Assuming that cursor CS1 is positioned on a rowset consisting of 10 rows in table T1, update all 10 rows in the rowset.
EXEC SQL UPDATE T1 SET C1 = 5 WHERE CURRENT OF CS1; 
Example 11
Assuming that cursor CS1 is positioned on a rowset consisting of 10 rows in table T1, update the fourth row of the rowset.
short ind1, ind2;

int n, updt_value;

stmt = 'UPDATE T1 SET C1 = ? WHERE CURRENT OF CS1 FOR ROW ? OF ROWSET'

ind1 = 0;

ind2 = 0;

n = 4;

updt_value = 5;

...

strcpy(my_sqlda.sqldaid,"SQLDA");

my_sqlda.sqln = 2;

my_sqlda.sqld = 2;

my_sqlda.sqlvar[0].sqltype = 497;
my_sqlda.sqlvar[0].sqllen = 4;
my_sqlda.sqlvar[0].sqldata = (int *) &updt_value;
my_sqlda.sqlvar[0].sqlind = (short *) &ind1;

my_sqlda.sqlvar[1].sqltype = 497;
my_sqlda.sqlvar[1].sqllen = 4;
my_sqlda.sqlvar[1].sqldata = (int *) &n;
my_sqlda.sqlvar[1].sqlind = (short *) &ind2;

EXEC SQL PREPARE S1 FROM :stmt;

EXEC SQL EXECUTE S1 USING DESCRIPTOR :my_sqlda;
Example 12
Start of changeAssume that table POLICY exists and that it is defined with a single period, BUSINESS_TIME. The table contains a row where column BK has a value of 'P138', column CLIENT has a value of 'C882', column TYPE has a value of 'PPO', and the period has value ('2013-01-01', '2020-12-31'). Update the portion of the row beginning from '2014-01-01' to set the TYPE column to 'HMO':
UPDATE POLICY
	FOR PORTION OF BUSINESS_TIME 
		FROM '2014-01-01' TO '9999-12-31'
		SET TYPE='HMO'
		WHERE BK='P138', CLIENT='C882';

After the UPDATE statement is processed, the table contains 2 rows in place of the original row. One row with period value ('2013-01-01', '2014-01-01') represents a value of 'PPO' for the TYPE column (the value before the update) and the other row with period value ('2014-01-01', '2020-12-31') represents a value of 'HMO' for the TYPE column (that began with the UPDATE statement).

End of change
Start of changeExample 13End of change
Start of change Suppose that the INTARRAY and CHARARRAY array types, the INTA, CHARA, and SI variables, and the T1 table are defined as follows:Start of change
CREATE TYPE INTARRAY AS INTEGER ARRAY [6];
CREATE TYPE CHARARRAY AS CHAR(20) ARRAY [7];
DECLARE INTA AS INTARRAY;
DECLARE CHARA AS CHARARRAY;
CREATE VARIABLE SI INT;
CREATE TABLE T1 (COL1 CHAR(7), COL2 INT);
End of change

Start of changeAssign values to CHARA, INTA, and SI.End of change

Start of change
SET CHARA = ARRAY [ 'a', 'b', 'c' ];
SET INTA = ARRAY [ 1, 2, 3, 4, 5 ];
SET SI = 1;
End of change

Start of changeInsert a row into table T1, and then update the row values using values from the CHARA and INTA arrays, which are indexed by the value of variable SI.End of change

Start of change
INSERT INTO T1 VALUES ('abc', 10);
UPDATE T1 
 SET COL1 = CHARA[SI],
 COL2 = INTA[SI];
End of change

Start of changeIn the table row, COL1 now contains 'a', and COL2 contains 1.End of change

Start of changeSet the value of column COL2 for all rows to the cardinality of array INTA.End of change

Start of change
UPDATE T1
 SET COL2 = CARDINALITY(INTA);
End of change

Start of changeIn the table row, COL2 now contains 5.End of change

End of change