The
UPDATE statement updates the values of specified columns in rows of
a table, view or nickname, or the underlying tables, nicknames, or
views of the specified fullselect.
Updating a row of a view updates a row of its base table,
if no INSTEAD OF trigger is defined for the update operation on this
view. If such a trigger is defined, the trigger will be executed instead.
Updating a row using a nickname updates a row in the data source object
to which the nickname refers.
The forms of this statement are:
- The Searched UPDATE form is used to update one or more
rows (optionally determined by a search condition).
- The Positioned UPDATE form is used to update exactly one
row (as determined by the current position of a cursor).
Invocation
An UPDATE statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. It is an executable statement that can be dynamically
prepared.
Authorization
The privileges held by the
authorization ID of the statement must include at least one of the
following authorities:
- UPDATE privilege on the target table, view, or nickname
- UPDATE
privilege on each of the columns that are to be updated, including
the columns of the BUSINESS_TIME period if a period-clause is specified
- CONTROL privilege on the target table, view, or nickname
- DATAACCESS authority
If a
row-fullselect is included
in the assignment, the privileges held by the authorization ID of
the statement must include at least one of the following authorities
for each referenced table, view, or nickname:
- SELECT privilege
- CONTROL privilege
- DATAACCESS authority
For each table, view, or nickname referenced by a subquery,
the privileges held by the authorization ID of the statement must
also include at least one of the following authorities:
- SELECT privilege
- CONTROL privilege
- DATAACCESS authority
If the package used to process the statement is precompiled
with SQL92 rules (option LANGLEVEL with a value of SQL92E or MIA),
and the searched form of an UPDATE statement includes a reference
to a column of the table, view, or nickname in the right side of the
assignment-clause,
or anywhere in the
search-condition, the
privileges held by the authorization ID of the statement must also
include at least one of the following authorities:
- SELECT privilege
- CONTROL privilege
- DATAACCESS authority
If the specified table or view is preceded by the ONLY
keyword, the privileges held by the authorization ID of the statement
must also include the SELECT privilege for every subtable or subview
of the specified table or view.
GROUP privileges are not checked
for static UPDATE statements.
If the target of the update operation
is a nickname, privileges on the object at the data source are not
considered until the statement is executed at the data source. At
this time, the authorization ID that is used to connect to the data
source must have the privileges that are required for the operation
on the object at the data source. The authorization ID of the statement
can be mapped to a different authorization ID at the data source.
Syntax (searched-update)
>>-UPDATE--+-+-table-name-+--+-------------------+-+------------>
| '-view-name--' '-| period-clause |-' |
+-nickname------------------------------+
+-ONLY--(--+-table-name-+--)------------+
| '-view-name--' |
'-(--fullselect--)----------------------'
>--+------------------------+--+---------------------+---------->
'-| correlation-clause |-' '-| include-columns |-'
>--SET--| assignment-clause |--+-------------------------+------>
'-WHERE--search-condition-'
>--+--------------+--------------------------------------------><
'-WITH--+-RR-+-'
+-RS-+
+-CS-+
'-UR-'
period-clause
|--FOR PORTION OF BUSINESS_TIME--FROM--value1--TO--value2-------|
Syntax (positioned-update)
>>-UPDATE--+-table-name-----------------+----------------------->
+-view-name------------------+
+-nickname-------------------+
'-ONLY--(--+-table-name-+--)-'
'-view-name--'
>--+------------------------+--SET--| assignment-clause |------->
'-| correlation-clause |-'
>--WHERE CURRENT OF--cursor-name-------------------------------><
correlation-clause
.-AS-.
|--+----+--correlation-name--+-----------------------+----------|
| .-,-----------. |
| V | |
'-(----column-name-+--)-'
include-columns
.-,----------------------.
V |
|--INCLUDE--(----column-name--data-type-+--)--------------------|
assignment-clause
.-,----------------------------------------------------------------------------------------.
V |
|----+-column-name--+----------------------+--=--+-expression-+-----------------------------+-+--|
| | .------------------. | +-NULL-------+ |
| | V | | '-DEFAULT----' |
| '---..attribute-name-+-' |
| .-,-------------------------------------. .-,------------------. |
| V | V (1) | |
'-(----column-name--+----------------------+-+--)--=--(--+---+-expression-+-----+-+--)-'
| .------------------. | | +-NULL-------+ |
| V | | | '-DEFAULT----' |
'---..attribute-name-+-' | (2) |
'-row-fullselect---------'
Notes:
- The number of expressions, NULLs and DEFAULTs must match
the number of column names.
- The number of columns in the select list must match the number
of column names.
Description
- table-name, view-name, nickname,
or (fullselect)
- Identifies
the object of the update operation. The name must identify one of
the following objects:
- A table, view, or nickname described in the catalog at the current
server
- A table or view at a remote server specified using a remote-object-name
The object must not be a catalog table, a view of a catalog table
(unless it is one of the updatable SYSSTAT views), a system-maintained
materialized query table, or a read-only view that has no INSTEAD
OF trigger defined for its update operations. If table-name is
a typed table, rows of the table or any of its proper subtables may
get updated by the statement. Only the columns of the specified table
may be set or referenced in the WHERE clause. For a positioned UPDATE,
the associated cursor must also have specified the same table, view
or nickname in the FROM clause without using ONLY.
If the
object of the update operation is a fullselect, the fullselect must
be updatable, as defined in the "Updatable views" Notes item in
the description of the CREATE VIEW statement.
If
the object of the update operation is a nickname, the extended indicator
variable values of DEFAULT and UNASSIGNED must not be used (SQLSTATE
22539).
For
additional restrictions related to temporal tables and use of a view
or fullselect as the target of the update operation,
see "Considerations for a system-period temporal table" and "Considerations for an application-period temporal table" in the Notes section of this topic.
- ONLY (table-name)
- Applicable to typed tables, the ONLY keyword specifies that the
statement should apply only to data of the specified table and rows
of proper subtables cannot be updated by the statement. For a positioned
UPDATE, the associated cursor must also have specified the table in
the FROM clause using ONLY. If table-name is
not a typed table, the ONLY keyword has no effect on the statement.
- ONLY (view-name)
- Applicable to typed views, the ONLY keyword specifies that the
statement should apply only to data of the specified view and rows
of proper subviews cannot be updated by the statement. For a positioned
UPDATE, the associated cursor must also have specified the view in
the FROM clause using ONLY. If view-name is
not a typed view, the ONLY keyword has no effect on the statement.
- period-clause
- Specifies
that a period clause applies to the target of the update operation.
If the target of the update operation is a view, the following conditions
apply to the view:
- The FROM clause of the outer fullselect of the view definition
must include a reference, directly or indirectly, to an application-period
temporal table (SQLSTATE 42724M).
- An INSTEAD OF UPDATE trigger must not be defined for the view
(SQLSTATE 428HY).
- FOR PORTION OF BUSINESS_TIME
- Specifies that the update only applies to row values for the portion
of the period in the row that is specified by the period clause. The
BUSINESS_TIME period must exist in the table (SQLSTATE 4274M).
- FROM value1 TO value2
- Specifies that the update applies to rows for the period specified
from value1 up to value2.
No rows are updated if value1 is greater
than or equal to value2, or if value1 or value2 is
the null value (SQLSTATE 02000).
For the period specified with FROM
value1 TO
value2,
the BUSINESS_TIME period in a row in the target of the update is in
any of the following states:
- Overlaps the beginning of the specified
period if the value of the begin column is less than value1 and
the value of the end column is greater than value1.
- Overlaps the end of the specified period
if the value of the end column is greater than or equal to value2 and
the value of the begin column is less than value2.
- Is fully
contained within the specified period if the value for the begin
column for BUSINESS_TIME is greater than or equal to value1 and
the value for the corresponding end column is less than or equal to value2.
- Is partially contained in the specified period if the row
overlaps the beginning of the specified period or the end of the specified
period, but not both.
- Fully overlaps the specified period if the period in the
row overlaps the beginning and end of the specified period.
- Is not contained in the period if both
columns of BUSINESS_TIME are less than or equal to value1 or
greater than or equal to value2.
If
the BUSINESS_TIME period in a row is not contained in the specified
period, the row is not updated. Otherwise, the update is applied based
on how the values in the columns of the BUSINESS_TIME period overlap
the specified period as follows:
- If
the BUSINESS_TIME period in a row is fully contained within the specified
period, the row is updated and the values of the begin column and
end column of BUSINESS_TIME are unchanged.
- If the BUSINESS_TIME period in a row is partially contained in
the specified period and overlaps the beginning of the specified period:
- The
row is updated. In the updated row, the value of the begin column
is set to value1 and the value of the end column
is the original value of the end column.
- A row is inserted using the original values from the row, except
that the end column is set to value1.
- If the BUSINESS_TIME period in a row is partially contained in
the specified period and overlaps the end of the specified period:
- The
row is updated. In the updated row, the value of the begin column
is the original value of the begin column and the end column is set
to value2.
- A row is inserted using the original values from the row, except
that the begin column is set to value2.
- If the BUSINESS_TIME period in a row fully overlaps the specified
period:
- The
row is updated. In the updated row the value of the begin column is
set to value1 and the value of the end column is
set to value2.
- A row is inserted using the original values from the row, except
that the end column is set to value1.
- An additional row is inserted using the original values from the
row, except that the begin column is set to value2.
- value1 and value2
- Each
expression must return a value that has a date data type, timestamp
data type, or a valid data type for a string representation of a date
or timestamp (SQLSTATE 428HY). The result of each expression must
be comparable to the data type of the columns of the specified period
(SQLSTATE 42884). See the comparison rules described in "Assignments
and comparisons".
Each expression can contain any of the following
supported operands (SQLSTATE 428HY):
- Constant
- Special register
- Variable
- Scalar function whose arguments are supported operands (though
user-defined functions and non-deterministic functions cannot be used)
- CAST specification where the cast operand is a supported operand
- Expression using arithmetic operators and operands
- correlation-clause
- Can be used within search-condition or assignment-clause to
designate a table, view, nickname, or fullselect. For a description
of correlation-clause, see "table-reference" in
the description of "Subselect".
- include-columns
- Specifies a set of columns that are included, along with the columns
of table-name or view-name,
in the intermediate result table of the UPDATE statement when it is
nested in the FROM clause of a fullselect. The include-columns are
appended at the end of the list of columns that are specified for table-name or view-name.
- INCLUDE
- Specifies a list of columns to be included in the intermediate
result table of the UPDATE statement.
- column-name
- Specifies a column of the intermediate result table of the UPDATE
statement. The name cannot be the same as the name of another include
column or a column in table-name or view-name (SQLSTATE
42711).
- data-type
- Specifies the data type of the include column. The data type must
be one that is supported by the CREATE TABLE statement.
- SET
- Introduces the assignment of values to column names.
- assignment-clause
-
- column-name
- Identifies a column to be updated. If extended
indicator variables are not enabled, the column-name must
identify an updatable column of the specified table, view, or nickname,
or identify an INCLUDE column. The object ID column of a typed
table is not updatable (SQLSTATE 428DZ). A column must not be specified
more than once, unless it is followed by ..attribute-name (SQLSTATE
42701).
If it specifies an INCLUDE column, the column name cannot
be qualified.
For a Positioned UPDATE:
- If the update-clause was specified in
the select-statement of the cursor, each
column name in the assignment-clause must
also appear in the update-clause.
- If the update-clause was not specified
in the select-statement of the cursor and
LANGLEVEL MIA or SQL92E was specified when the application was precompiled,
the name of any updatable column may be specified.
- If the update-clause was not specified
in the select-statement of the cursor and
LANGLEVEL SAA1 was specified either explicitly or by default when
the application was precompiled, no columns may be updated.
- ..attribute-name
- Specifies the attribute of a structured type that is set (referred
to as an attribute assignment. The column-name specified
must be defined with a user-defined structured type (SQLSTATE 428DP).
The attribute-name must be an attribute of the structured type of column-name (SQLSTATE
42703). An assignment that does not involve the ..attribute-name clause
is referred to as a conventional assignment.
- expression
- Indicates the new value of the column. The expression is any expression
of the type described in "Expressions". The expression cannot
include an aggregate function except when it occurs within a scalar
fullselect (SQLSTATE 42903).
An expression may
contain references to columns of the target table of the UPDATE statement.
For each row that is updated, the value of such a column in an expression
is the value of the column in the row before the row is updated.
An expression
cannot contain references to an INCLUDE column. If expression is
a single host variable, the host variable can include an indicator
variable that is enabled for extended indicator variables. If extended
indicator variables are enabled, the extended indicator variable values
of default (-5) or unassigned (-7) must not be used (SQLSTATE 22539)
if either of the following statements is true:
- The expression is more complex than a single host variable with
explicit casts
- The target column has data type of structured type
- NULL
- Specifies the null value and can only be specified for nullable
columns (SQLSTATE 23502). NULL cannot be the value in an attribute
assignment (SQLSTATE 429B9) unless it is specifically cast to the
data type of the attribute.
- DEFAULT
- Specifies that the default value should be used based on how the
corresponding column is defined in the table. The value that is inserted
depends on how the column was defined.
- If the column was defined as a generated column based on an expression,
the column value will be generated by the system, based on the expression.
- If the column was defined using the IDENTITY clause, the value
is generated by the database manager.
- If the column was defined using the WITH DEFAULT clause, the value
is set to the default defined for the column (see default-clause in "ALTER
TABLE").
- If the column was defined using the NOT NULL clause and the GENERATED
clause was not used, or the WITH DEFAULT clause was not used, or DEFAULT
NULL was used, the DEFAULT keyword cannot be specified for that column
(SQLSTATE 23502).
- If the column was defined using the ROW CHANGE TIMESTAMP clause,
the value is generated by the database manager.
The only value that a generated column defined with the GENERATED
ALWAYS clause can be set to is DEFAULT (SQLSTATE 428C9).
The
DEFAULT keyword cannot be used as the value in an attribute assignment
(SQLSTATE 429B9).
The DEFAULT keyword cannot be used as the
value in an assignment for update on a nickname where the data source
does not support DEFAULT syntax.
- row-fullselect
- Specifies a fullselect that returns a single row. The result
column values are assigned to each corresponding column-name.
If the fullselect returns no rows, the null value is assigned to each
column; an error occurs if any column to be updated is not nullable.
An error also occurs if there is more than one row in the result.
A row-fullselect may
contain references to columns of the target table of the UPDATE statement.
For each row that is updated, the value of such a column in an expression
is the value of the column in the row before the row is updated. An
error is returned if there is more than one row in the result (SQLSTATE
21000).
- WHERE
- Introduces a condition that indicates what rows are updated. You
can omit the clause, give a search condition, or name a cursor. If
the clause is omitted, all rows of the table, view or nickname are
updated.
- search-condition
- Each column-name in the search condition,
other than in a subquery, must name a column of the table, view or
nickname. When the search condition includes a subquery in which the
same table is the base object of both the UPDATE and the subquery,
the subquery is completely evaluated before any rows are updated.
The
search-condition is applied to each row of the table, view or nickname
and the updated rows are those for which the result of the search-condition
is true.
If the search condition contains a subquery, the subquery
can be thought of as being executed each time the search condition
is applied to a row, and the results used in applying the search condition.
In actuality, a subquery with no correlated references is executed
only once, whereas a subquery with a correlated reference may have
to be executed once for each row.
- CURRENT OF cursor-name
- Identifies the cursor to be used in the update operation. The cursor-name must
identify a declared cursor, explained in "DECLARE CURSOR". The
DECLARE CURSOR statement must precede the UPDATE statement in the
program.
The specified table, view, or nickname must also be named
in the FROM clause of the SELECT statement of the cursor, and the
result table of the cursor must not be read-only. (For an explanation
of read-only result tables, see "DECLARE CURSOR".)
When
the UPDATE statement is executed, the cursor must be positioned on
a row; that row is updated.
This form of UPDATE cannot be
used (SQLSTATE 42828) if the cursor references:
- A view on which an INSTEAD OF UPDATE trigger is defined
- A view that includes an OLAP function in the select list of the
fullselect that defines the view
- A view that is defined, either directly or indirectly, using the
WITH ROW MOVEMENT clause
- WITH
- Specifies the isolation level at which the UPDATE statement is
executed.
- RR
- Repeatable Read
- RS
- Read Stability
- CS
- Cursor Stability
- UR
- Uncommitted Read
The default isolation level of the statement is
the isolation level of the package in which the statement is bound.
The WITH clause has no effect on nicknames, which always use the default
isolation level of the statement.
Rules
- Triggers: UPDATE statements may cause triggers to
be executed. A trigger may cause other statements to be executed,
or may raise error conditions based on the update values. If an update
operation on a view causes an INSTEAD OF trigger to fire, validity,
referential integrity, and constraints will be checked against the
updates that are performed in the trigger, and not against the view
that caused the trigger to fire, or its underlying tables.
- Assignment: Update values are assigned to columns
according to specific assignment rules.
- Validity: The updated row must conform to any constraints
imposed on the table (or on the base table of the view) by any unique
index on an updated column.
If a view is used that is not defined
using WITH CHECK OPTION, rows can be changed so that they no longer
conform to the definition of the view. Such rows are updated in the
base table of the view and no longer appear in the view.
If
a view is used that is defined using WITH CHECK OPTION, an updated
row must conform to the definition of the view. For an explanation
of the rules governing this situation, see "CREATE VIEW".
- Check constraint: Update value must satisfy the
check-conditions of the check constraints defined on the table.
An
UPDATE to a table with check constraints defined has the constraint
conditions for each column updated evaluated once for each row that
is updated. When processing an UPDATE statement, only the check constraints
referring to the updated columns are checked.
- Referential integrity: The value of the parent unique
keys cannot be changed if the update rule is RESTRICT and there are
one or more dependent rows. However, if the update rule is NO ACTION,
parent unique keys can be updated as long as every child has a parent
key by the time the update statement completes. A non-null update
value of a foreign key must be equal to a value of the primary key
of the parent table of the relationship.
- XML values: When an XML column value is updated,
the new value must be a well-formed XML document (SQLSTATE 2200M).
- Security policy: If the identified table or the
base table of the identified view is protected with a security policy,
the session authorization ID must have the label-based access control
(LBAC) credentials that allow:
- Write access to all protected columns that are being updated (SQLSTATE
42512)
- Write access for any explicit value provided for a DB2SECURITYLABEL
column for security policies that were created with the RESTRICT NOT
AUTHORIZED WRITE SECURITY LABEL option (SQLSTATE 23523)
- Read and write access to all rows that are being updated (SQLSTATE
42519)
The session authorization ID must also have been granted a
security label for write access for the security policy if an implicit
value is used for a DB2SECURITYLABEL column (SQLSTATE 23523), which
can happen when:
- The DB2SECURITYLABEL column is not included in the list of columns
that are to be updated (and so it will be implicitly updated to the
security label for write access of the session authorization ID)
- A value for the DB2SECURITYLABEL column is explicitly provided
but the session authorization ID does not have write access for
that value, and the security policy is created with the OVERRIDE
NOT AUTHORIZED WRITE SECURITY LABEL option
- Extended
indicator variable usage: If enabled, indicator variable values
other than 0 (zero) through -7 must not be input (SQLSTATE 22010).
Also, if enabled, the default and unassigned extended indicator variable
values must not appear in contexts in which they are not supported
(SQLSTATE 22539).
- Extended
indicator variables: In the assignment-clause of
an UPDATE statement, an expression that
is a reference to a single host variable, or a host variable being
explicitly cast can result in assigning an extended indicator variable
value. Assigning an extended indicator variable-based value of unassigned
has the effect of leaving the target column set to its current value,
as if it had not been specified in the statement. Assigning an extended
indicator variable-based value of default assigns the default value
of the column. For information about default values of data types,
see the description of the DEFAULT clause in
the "CREATE TABLE" statement.
If a target column is not updatable
(for example, a column in a view that is defined as an expression),
then it must be assigned the extended indicator variable-based value
of unassigned (SQLSTATE 42808).
If the target column is a column
defined as GENERATED ALWAYS, then it must be assigned the DEFAULT
keyword, or the extended indicator variable-based values of default
or unassigned (SQLSTATE 428C9).
The UPDATE statement must not
assign all target columns to an extended indicator variable-based
value of unassigned (SQLSTATE 22540).
Notes
- If an update value violates any constraints, or if any other error
occurs during the execution of the UPDATE statement, no rows are updated.
The order in which multiple rows are updated is undefined.
- An update to a view defined using the WITH ROW MOVEMENT clause
could cause a delete operation and an insert operation against the
underlying tables of the view. For details, see the description of
the CREATE VIEW statement.
- When an UPDATE statement completes execution, the value of SQLERRD(3)
in the SQLCA is the number of rows that qualified for the update operation.
In the context of an SQL procedure statement, the value can be retrieved
using the ROW_COUNT variable of the GET DIAGNOSTICS statement. The
SQLERRD(5) field contains the number of rows inserted, deleted, or
updated by all activated triggers.
- Unless appropriate locks already exist, one or more exclusive
locks are acquired by the execution of a successful UPDATE statement.
Until the locks are released, the updated row can only be accessed
by the application process that performed the update (except for applications
using the Uncommitted Read isolation level). For further information
on locking, see the descriptions of the COMMIT, ROLLBACK, and LOCK
TABLE statements.
- When updating the column distribution statistics for a typed table,
the subtable that first introduced the column must be specified.
- Multiple attribute assignments on the same structured type column
occur in the order specified in the SET clause and, within a parenthesized
set clause, in left-to-right order.
- An attribute assignment invokes the mutator method for the attribute
of the user-defined structured type. For example, the assignment st..a1=x has
the same effect as using the mutator method in the assignment st
= st..a1(x).
- While a given column may be a target column in only one conventional
assignment, a column may be a target column in multiple attribute
assignments (but only if it is not also a target column in a conventional
assignment).
- When an identity column defined as a distinct type is updated,
the entire computation is done in the source type, and the result
is cast to the distinct type before the value is actually assigned
to the column. (There is no casting of the previous value to the
source type before the computation.)
- To have DB2® generate a value
on a SET statement for an identity column, use the DEFAULT keyword:
SET NEW.EMPNO = DEFAULT
In this example,
NEW.EMPNO is defined as an identity column, and the value used to
update this column is generated by DB2.
- For more information about consuming values of a generated sequence
for an identity column, or about exceeding the maximum value for an
identity column, see "INSERT".
- With partitioned tables, an UPDATE WHERE CURRENT OF cursor-name operation
can move a row from one data partition to another. After this occurs,
the cursor is no longer positioned on the row, and no further UPDATE
WHERE CURRENT OF cursor-name modifications
to that row are possible. The next row in the cursor can be fetched,
however.
- For a column defined using the ROW CHANGE TIMESTAMP clause, the
value is always changed on update of the row. If the column is not
specified in the SET list explicitly, the database manager still generates
a value for that row. The value is unique for each table partition
within the database partition and is set to the approximate timestamp
corresponding to the row update.
- Extended
indicator variables and update triggers: If a target column has
been assigned with an extended indicator variable-based value of unassigned,
that column is not considered to have been updated. That column is
treated as if it had not been specified in the OF column-name list
of any update trigger defined on the target table.
- Extended
indicator variables and deferred error checks: When extended indicator
variables are enabled, validation that would otherwise be done in
statement preparation, to recognize an update of a non-updatable column,
is deferred until statement execution, except for column level update
privilege checking of static UPDATE statements. Whether an error should
be reported can be determined only during execution based on the indicator
value. The checking of column level update privilege for static UPDATE
statements continues to be performed during bind processing even when
extended indicator variables are enabled.
- Considerations for a system-period temporal table: The
target of the UPDATE statement must not be a fullselect that references
a view in the FROM clause followed by a period specification for SYSTEM_TIME
if the view is defined with the WITH CHECK OPTION and the view definition
includes a WHERE clause containing one of the following syntax elements
(SQLSTATE 51046):
- A subquery that references a system-period temporal table (directly
or indirectly)
- An invocation of an SQL routine that has a package associated
with it
- An invocation of an external routine with a data access indication
other than NO SQL
If the CURRENT TEMPORAL SYSTEM_TIME special register is set
to a non-null value, an underlying target of the UPDATE statement
must not be a system-period temporal table (SQLSTATE 51046), and the
target of the UPDATE statement must not be a view defined with the
WITH CHECK OPTION if the view definition includes a WHERE clause containing
one of the following syntax elements (SQLSTATE 51046):
- A subquery that references a system-period temporal table (directly
or indirectly)
- An invocation of an SQL routine that has a package associated
with it
- An invocation of an external routine with a data access indication
other than NO SQL
When a row of a system-period temporal table is updated,
the database manager updates the values of the row-begin and transaction-start-ID
columns as follows:
- A row-begin column is assigned a value that is generated using
a reading of the time-of-day clock during execution of the first data
change statement in the transaction that requires a value to be assigned
to the row begin or transaction start-ID column in a table, or a row
in a system-period temporal table is deleted. The database manager
ensures uniqueness of the generated values for a row-begin column
across transactions. The timestamp value might be adjusted to ensure
that rows inserted into an associated history table have the end timestamp
value greater than the begin timestamp value which can happen when
a conflicting transaction is updating the same row in the system-period
temporal table. The database configuration parameter systime_period_adj must
be set to Yes for this adjustment in the timestamp value to occur.
If multiple rows are updated within a single SQL transaction and an
adjustment is not needed, the values for the row-begin column are
the same for all the rows and are unique from the values generated
for the column for another transaction.
- A transaction start-ID column is assigned a unique timestamp value
per transaction or the null value The null value is assigned to the
transaction start-ID column if the column is nullable and there is
a row-begin column in the table for which the value did not need to
be adjusted. Otherwise, the value is generated using a reading of
the time-of-day clock during execution of the first data change statement
in the transaction that requires a value to be assigned to the row
begin or transaction start-ID column in a table, or a row in a system-period
temporal table is deleted. If multiple rows are updated within a single
SQL transaction, the values for the transaction start-ID column are
the same for all the rows and are unique from the values generated
for the column for another transaction.
If the UPDATE statement has a search condition containing
a correlated subquery that references historical rows (explicitly
referencing the name of the history table name or implicitly through
the use of a period specification in the FROM clause), the old version
of the updated rows that are inserted as historical rows (into the
history table if any) are potentially visible to update operations
for the rows subsequently processed for the statement.
The
target of an UPDATE statement cannot be a fullselect that references
a view in the FROM clause followed by a period specification for SYSTEM_TIME
if both of the following conditions are true (SQLSTATE 51046):
- The view is defined with the WITH CHECK OPTION.
- The view definition includes a WHERE clause containing one of
the following syntax elements:
- A subquery that references a system-period temporal table (directly
or indirectly).
- An invocation of an SQL routine that has a package associated
with it.
- An invocation of an external routine with a data access indication
other than NO SQL.
If
the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null
value, the underlying target (direct or indirect) of the UPDATE statement
cannot be a system-period temporal table (SQLSTATE 51046).
If
the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null
value, the target of an UPDATE statement cannot be a view defined
with the WITH CHECK OPTION if the view definition includes a WHERE
clause containing one of the following syntax elements (SQLSTATE 51046):
- A subquery that references a system-period temporal table (directly
or indirectly).
- An invocation of an SQL routine that has a package associated
with it.
- An invocation of an external routine with a data access indication
other than NO SQL.
- Considerations for a history table: When
a row of a system-period temporal table is updated, a historical copy
of the row is inserted into the corresponding history table and the
end timestamp of the historical row is captured in the form of a system
determined value that corresponds to the time of the data change operation.
The database manager assigns the value that is generated using a reading
of the time-of-day clock during execution of the first data change
statement in the transaction that requires a value to be assigned
to the row begin or transaction start-ID column in a table, or a row
in a system-period temporal table is deleted. The database manager
ensures uniqueness of the generated values for an end column in a
history table across transactions. The timestamp value might be adjusted
to ensure that rows inserted into the history table have the end timestamp
value greater than the begin timestamp value which can happen when
a conflicting transaction is updating the same row in the system-period
temporal table (SQLSTATE 01695). The database configuration parameter systime_period_adj must
be set to Yes for this adjustment in the timestamp value to occur.
For
an update operation, the adjustment only affects the value for the
end column corresponding to the row-end column in the history table
associated with the system-period temporal table. Take these adjustments
into consideration on subsequent references to the table whether there
is a search for the transaction start time in the values for the columns
corresponding to the row-begin and row-end columns of the period in
the associated system-period temporal table.
- Considerations for an application-period temporal table: The
target of the UPDATE statement must not be a fullselect that references
a view in the FROM clause followed by a period specification for BUSINESS_TIME
if the view is defined with the WITH CHECK OPTION and the view definition
includes a WHERE clause containing one of the following syntax elements
(SQLSTATE 51046):
- A subquery that references an application-period temporal table
(directly or indirectly)
- An invocation of an SQL routine that has a package associated
with it
- An invocation of an external routine with a data access indication
other than NO SQL
If the CURRENT TEMPORAL BUSINESS_TIME special register is
set to a non-null value, the target of the UPDATE statement must not
be a view defined with the WITH CHECK option if the view definition
includes a WHERE clause containing one of the following syntax elements
(SQLSTATE 51046):
- A subquery that references an application-period temporal table
(directly or indirectly)
- An invocation of an SQL routine that has a package associated
with it
- An invocation of an external routine with a data access indication
other than NO SQL
An UPDATE statement for an application-period temporal
table that contains a FOR PORTION OF BUSINESS_TIME clause indicates
between which two points in time that the specified updates are effective.
When FOR PORTION OF BUSINESS_TIME is specified and the period value
for a row, specified by the values of the row-begin column and row-end
column, is only partially contained in the period specified from value1 up
to value2, the row is updated and one or
two rows are automatically inserted to represent the portion of the
row that is not changed. New values are generated for each generated
column in an application-period temporal table for each row that is
automatically inserted as a result of an update operation on the table.
If a generated column is defined as part of a unique or primary key,
parent key in a referential constraint, or unique index, it is possible
that an automatic insert will violate a constraint or index in which
case an error is returned.
When a row is inserted into an application-period
temporal table that has either a primary key or unique constraint
with the BUSINESS_TIME WITHOUT OVERLAPS clause defined, or a unique
index with the BUSINESS_TIME WITHOUT OVERLAPS clause defined, if the
period defined by the begin and end columns of the BUSINESS_TIME period
overlap the period defined by the begin and end columns of the BUSINESS_TIME
period for another row with the same unique constraint or unique index
in the table, an error is returned.
The
target of an UPDATE statement cannot be a fullselect that references
a view in the FROM clause followed by a period specification for BUSINESS_TIME
if both of the following conditions are true (SQLSTATE 51046):
- The view is defined with the WITH CHECK OPTION.
- The view definition includes a WHERE clause containing one of
the following syntax elements:
- A subquery that references an application-period temporal table
(directly or indirectly).
- An invocation of an SQL routine that has a package associated
with it.
- An invocation of an external routine with a data access indication
other than NO SQL.
If
the CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null
value, the target of an UPDATE statement cannot be a view defined
with the WITH CHECK OPTION if the view definition includes a WHERE
clause containing one of the following syntax elements (SQLSTATE 51046):
- A subquery that references an application-period temporal table
(directly or indirectly).
- An invocation of an SQL routine that has a package associated
with it.
- An invocation of an external routine with a data access indication
other than NO SQL.
When
an application-period temporal table is the target of an UPDATE statement,
the value in effect for the CURRENT TEMPORAL BUSINESS_TIME special
register is not the null value, and the BUSTIMESENSITIVE bind option
is set to YES, the following additional predicates are implicit:
bt_begin <= CURRENT TEMPORAL BUSINESS_TIME
AND bt_end > CURRENT TEMPORAL BUSINESS_TIME
where
bt_begin and
bt_end are
the begin and end columns of the BUSINESS_TIME period of the target
table of the UPDATE statement.
- Considerations
for application-period temporal tables and triggers: When a row
is updated and the FOR PORTION OF BUSINESS_TIME clause is specified,
additional rows may be implicitly inserted to reflect any portion
of the row that was not updated. Any existing update triggers are
activated for the rows updated, and any existing insert triggers are
activated for rows that are implicitly inserted.
Examples
- Example 1: Change the job (JOB) of employee number (EMPNO)
'000290' in the EMPLOYEE table to 'LABORER'.
UPDATE EMPLOYEE
SET JOB = 'LABORER'
WHERE EMPNO = '000290'
- Example 2: Increase the project staffing (PRSTAFF) by
1.5 for all projects that department (DEPTNO) 'D21' is responsible
for in the PROJECT table.
UPDATE PROJECT
SET PRSTAFF = PRSTAFF + 1.5
WHERE DEPTNO = 'D21'
- Example 3: All the employees except the manager of department
(WORKDEPT) 'E21' have been temporarily reassigned. Indicate this by
changing their job (JOB) to the null value and their pay (SALARY,
BONUS, COMM) values to zero in the EMPLOYEE table.
UPDATE EMPLOYEE
SET JOB=NULL, SALARY=0, BONUS=0, COMM=0
WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'
This
statement could also be written as follows.
UPDATE EMPLOYEE
SET (JOB, SALARY, BONUS, COMM) = (NULL, 0, 0, 0)
WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'
- Example 4: Update the salary and the commission column
of the employee with employee number 000120 to the average of the
salary and of the commission of the employees of the updated row's
department, respectively.
UPDATE (SELECT EMPNO, SALARY, COMM,
AVG(SALARY) OVER (PARTITION BY WORKDEPT),
AVG(COMM) OVER (PARTITION BY WORKDEPT)
FROM EMPLOYEE E) AS E(EMPNO, SALARY, COMM, AVGSAL, AVGCOMM)
SET (SALARY, COMM) = (AVGSAL, AVGCOMM)
WHERE EMPNO = '000120'
The previous statement
is semantically equivalent to the following statement, but requires
only one access to the EMPLOYEE table, whereas the following statement
specifies the EMPLOYEE table twice. UPDATE EMPLOYEE EU
SET (EU.SALARY, EU.COMM)
=
(SELECT AVG(ES.SALARY), AVG(ES.COMM)
FROM EMPLOYEE ES
WHERE ES.WORKDEPT = EU.WORKDEPT)
WHERE EU.EMPNO = '000120'
- Example 5: In a C program display the rows from the EMPLOYEE
table and then, if requested to do so, change the job (JOB) of certain
employees to the new job keyed in.
EXEC SQL DECLARE C1 CURSOR FOR
SELECT *
FROM EMPLOYEE
FOR UPDATE OF JOB;
EXEC SQL OPEN C1;
EXEC SQL FETCH C1 INTO ... ;
if ( strcmp (change, "YES") == 0 )
EXEC SQL UPDATE EMPLOYEE
SET JOB = :newjob
WHERE CURRENT OF C1;
EXEC SQL CLOSE C1;
- Example 6: These examples mutate attributes of column
objects.
Assume that the following types and tables exist:
CREATE TYPE POINT AS (X INTEGER, Y INTEGER)
NOT FINAL WITHOUT COMPARISONS
MODE DB2SQL
CREATE TYPE CIRCLE AS (RADIUS INTEGER, CENTER POINT)
NOT FINAL WITHOUT COMPARISONS
MODE DB2SQL
CREATE TABLE CIRCLES (ID INTEGER, OWNER VARCHAR(50), C CIRCLE
The
following example updates the CIRCLES table by changing the OWNER
column and the RADIUS attribute of the CIRCLE column where the ID
is 999:
UPDATE CIRCLES
SET OWNER = 'Bruce'
C..RADIUS = 5
WHERE ID = 999
The following example
transposes the X and Y coordinates of the center of the circle identified
by 999:
UPDATE CIRCLES
SET C..CENTER..X = C..CENTER..Y,
C..CENTER..Y = C..CENTER..X
WHERE ID = 999
The following example
is another way of writing both of the previous statements. This example
combines the effects of both of the previous examples:
UPDATE CIRCLES
SET (OWNER,C..RADIUS,C..CENTER..X,C..CENTER..Y) =
('Bruce',5,C..CENTER..Y,C..CENTER..X)
WHERE ID = 999
- Example 7: Update the XMLDOC column of the DOCUMENTS table
with DOCID '001' to the character string that is selected and parsed
from the XMLTEXT table.
UPDATE DOCUMENTS SET XMLDOC =
(SELECT XMLPARSE(DOCUMENT C1 STRIP WHITESPACE)
FROM XMLTEXT WHERE TEXTID = '001')
WHERE DOCID = '001'