UPDATE
The UPDATE statement updates the values of specified columns in rows of a table or view. Updating a row of a view updates a row of its base table, if no INSTEAD OF UPDATE trigger is defined on this view. If such a trigger is defined, the trigger will be activated instead.
There are two forms of this statement:
- The Searched UPDATE form is used to update one or more rows (optionally determined by a search condition).
- The Positioned UPDATE form is used to update exactly one row (as determined by the current position of a cursor).
Invocation
A Searched UPDATE statement can be embedded in an application program or issued interactively. A Positioned UPDATE must be embedded in an application program. Both forms are executable statements that can be dynamically prepared.
Authorization
The privileges held by the authorization ID of the statement must include at least one of the following:
- For the table or view identified in the statement:
- The UPDATE privilege on the table or view, or
- The UPDATE privilege on each column to be updated; and
- The system authority *EXECUTE on the library containing the table or view
- Administrative authority
If the expression in the assignment-clause contains a reference to a column of the table or view, or if the search-condition in a Searched UPDATE contains a reference to a column of the table or view, then the privileges held by the authorization ID of the statement must also include one of the following:
- The SELECT privilege on the table or view
- Administrative authority
If the search-condition includes a subquery or if the assignment-clause includes a scalar-fullselect or row-fullselect, see Queries for an explanation of the authorization required for each subselect.
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.
Syntax
Positioned UPDATE:>>-UPDATE--+-table-name-+--+--------------------+---------------> '-view-name--' '-correlation-clause-' >--+-------------------------+--SET--assignment-clause----------> +-OVERRIDING SYSTEM VALUE-+ '-OVERRIDING USER VALUE---' >--+-------------------------+----------------------------------> '-WHERE--search-condition-' .-----------------------------------------. V | >----+-------------------------------------+-+----------------->< +-isolation-clause--------------------+ '-concurrent-access-resolution-clause-'
Description
- table-name or view-name
- Identifies the table or view to be updated. The name must identify a table or view that exists at the current server, but it must not identify a catalog table, a view of a catalog table, or a read-only view. For an explanation of read-only views and updatable views, see CREATE VIEW.
- correlation-clause
- Can be used within search-condition or assignment-clause to designate the table or view. For an explanation of correlation-clause, see table-reference. For an explanation of correlation-name, see Correlation names.
- OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE
- Specifies whether system-generated values or user-specified values
for a ROWID, identity, or row change timestamp column are used. If
OVERRIDING SYSTEM VALUE is specified, the implicit or explicit list
of columns in the SET clause must contain a column defined as GENERATED
ALWAYS. If OVERRIDING USER VALUE is specified, the implicit or explicit
list of columns in the SET clause must contain a column defined as
either GENERATED ALWAYS or GENERATED BY DEFAULT.
- OVERRIDING SYSTEM VALUE
- Specifies that the value specified in the SET clause for a column that is defined as GENERATED ALWAYS is used. A system-generated value is not used.
- OVERRIDING USER VALUE
- Specifies that the value specified in the SET clause for a column that is defined as either GENERATED ALWAYS or GENERATED BY DEFAULT is ignored. Instead, a system-generated value is used, overriding the user-specified value.
If neither OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE is specified:
- A value cannot be specified for a ROWID, identity, or row change timestamp column that is defined as GENERATED ALWAYS.
- A value can be specified for a ROWID column that is defined as GENERATED BY DEFAULT. If a value is specified, that value is assigned to the column. However, a value in a ROWID column defined BY DEFAULT can be updated only if the specified value is a valid row ID value that was previously generated by DB2® for z/OS® or DB2 for i.
- A value can be specified for an identity or row change timestamp
column that is defined as GENERATED BY DEFAULT. When a value of an
identity column or row change timestamp column defined BY DEFAULT
is updated, the database manager does not verify that the specified
value is a unique value for the column unless the identity column
or row change timestamp column is the sole key in a unique constraint
or unique index. Without a unique constraint or unique index, the
database manager can guarantee unique values only among the set of
system-generated values as long as NO CYCLE is in effect.
If a value is not specified the database manager generates a new value.
- SET
- Introduces the assignment of values to column names.
- assignment-clause
-
- column-name
- Identifies
a column to be updated. The column-name must
identify a column of the specified table or view. If extended indicator
variables are not enabled, that column must not identify a view column
derived from a scalar function, constant, or expression. A column
must not be specified more than once.
For a Positioned UPDATE:
- If the UPDATE clause was specified in the SELECT statement of the cursor, each column name in the SET list must also appear in the UPDATE clause.
- If the UPDATE clause was not specified in the SELECT statement of the cursor, the name of any updatable column may be specified.
For more information, see update-clause.
A view column derived from the same column as another column of the view can be updated, but both columns cannot be updated in the same UPDATE statement.
If a list of column-names is specified, the number of expressions, NULLs, and DEFAULTS must match the number of column-names.
- ROW
- Identifies
all the columns of the specified table or view except for columns
defined with the hidden attribute. If a view is specified, none of
the columns of the view may be derived from a scalar function, constant,
or expression.
The number of expressions, NULLs, and DEFAULTs (or the number of result columns from a row-fullselect) must match the number of columns in the row.
For a Positioned UPDATE, if the UPDATE clause was specified in the SELECT statement of the cursor, each column of the table or view must also appear in the UPDATE clause. For more information, see update-clause.
ROW may not be specified for a view that contains a view column derived from the same column as another column of the view, because both columns cannot be updated in the same UPDATE statement.
- expression
- Specifies
the new value of the column. The expression is
any expression of the type described in Expressions.
It must not include an aggregate function.
A column-name in an expression must name a column of the named table or view. For each row updated, the value of the column in the expression is the value of the column in the row before the row is updated.
Each variable in the clause must identify a host structure or variable that is declared in accordance with the rules for declaring host structures and variables. In the operational form of the statement, a reference to a host structure is replaced by a reference to each of its variables. If expression is a single host variable, the host variable can include an indicator that is enabled for extended indicator variables. If extended indicator variables are enabled and an expression in the assignment clause is not a single host variable, the extended indicator variable values of DEFAULT and UNASSIGNED must not be used. For further information about variables and structures, see References to host variables and Host structures. If a host structure is specified, the keyword ROW must be specified.
- NULL
- Specifies the new value for a column is the null value. NULL should only be specified for nullable columns.
- DEFAULT
- Specifies
that the default value is assigned to a column. The value that is
used depends on how the column was defined, as follows:
- If the column is a ROWID or identity column, the database manager will generate a new value.
- If the WITH DEFAULT clause is used, the default used is as defined for the column (see default-clause in column-definition in CREATE TABLE).
- If the WITH DEFAULT clause or the NOT NULL clause is not used, the value used is NULL.
- If the column is defined as a row change timestamp column, a new row change timestamp value is assigned to the column.
If the NOT NULL clause is used and the WITH DEFAULT clause is not used, or if DEFAULT NULL is used, the DEFAULT keyword cannot be specified for that column.
DEFAULT must be specified for an identity column defined as GENERATED ALWAYS unless OVERRIDING SYSTEM VALUE is used.
- row-fullselect
- A
fullselect that returns a single result row. The number of result
columns in the select list must match the number of column-names (or
if ROW is specified, the number of columns in the row) specified for
assignment. The result column values are assigned to each corresponding column-name.
If the result of the fullselect is no rows, then null values are assigned.
An error is returned if there is more than one row in the result.
The row-fullselect may contain references to columns of the target table of the UPDATE statement. For each row updated, the value of such a column in the expression is the value of the column in the row before the row is updated.
- WHERE
- Specifies the
rows to be updated. The clause can be omitted, or a search-condition or cursor-name can
be specified. If the clause is omitted, all rows of the table or view
are updated.
- search-condition
- Is
any search described in Search conditions. Each column-name in
the search condition, other than in a subquery, must name a column
of the table or view. When the search condition includes a subquery
in which the same table is the base object of both the UPDATE and
the subquery, the subquery is completely evaluated before any rows
are updated.
The search-condition is applied to each row of the table or view. The updated rows are those for which the results of the search-condition are true.
If the search-condition contains a subquery, the subquery can be thought of as being executed each time the search-condition is applied to a row, and the results of that subquery used in applying the search-condition. In actuality, a subquery with no correlated references may be executed only once. A subquery with a correlated reference may have to be executed once for each row.
- CURRENT OF cursor-name
- Identifies the cursor to be
used in the update operation. The cursor-name must
identify a declared cursor as explained in DECLARE CURSOR.
The table or view named must also be named in the FROM clause of the SELECT statement of the cursor, and the result table of the cursor must not be read-only. For an explanation of read-only result tables, see DECLARE CURSOR.
When the UPDATE statement is executed, the cursor must be positioned on a row; that row is updated.
- isolation-clause
- Specifies
the isolation level to be used for this statement.
- WITH
- Introduces the isolation level, which may be one of:
- RR Repeatable read
- RS Read stability
- CS Cursor stability
- UR Uncommitted read
- NC No commit
concurrent-access-resolution-clause
- Specifies the concurrent access resolution
to use for the select statement. For more information, see concurrent-access-resolution-clause.
UPDATE Rules
Assignment: Update values are assigned to columns in accordance with the storage assignment rules described in Assignments and comparisons.
Validity: Updates must obey the following rules. If they do not, or if any other errors occur during the execution of the UPDATE statement, no rows are updated.
- Fullselects: The row-fullselect or scalar-fullselect shall return no more than one row (SQLSTATE 21000).
- Unique constraints and unique indexes: If the identified
table, or the base table of the identified view, has one or more unique
indexes or unique constraints, each row update in the table must conform
to the limitations imposed by those indexes and constraints (SQLSTATE
23505).
All uniqueness checks are effectively made at the end of the statement. In the case of a multiple-row UPDATE statement of a column involved in a unique index or unique constraint, this would occur after all rows were updated.
- Check constraints: If the identified table, or the
base table of the identified view, has one or more check constraints,
each check constraint must be true or unknown for each row updated
in the table (SQLSTATE 23513).
All check constraints are effectively validated at the end of the statement. In the case of a multiple-row UPDATE statement, this would occur after all rows were updated.
- Views and the WITH CHECK OPTION: If a view is identified, the updated rows must conform to any applicable WITH CHECK OPTION (SQLSTATE 44000). For more information, see CREATE VIEW.
Triggers: If the identified table or the base table of the identified view has an update trigger, the trigger is activated. A trigger might cause other statements to be executed or raise error conditions based on the updated values.
Referential Integrity: The value of the parent key in a parent row must not be changed.
If the update values produce a foreign key that is nonnull, the foreign key must be equal to some value of the parent key of the parent table of the relationship.
The referential constraints (other than a referential constraint with a RESTRICT delete rule) are effectively checked at the end of the statement. In the case of a multiple-row UPDATE statement, this would occur after all rows were updated.
XML values: When an XML column is updated,
the new value must be a well-formed XML document.
Extended indicator variable usage: If enabled, indicator variable values other than positive values and 0 (zero) through -7 must not be used. The DEFAULT and UNASSIGNED extended indicator variable values must not appear in contexts in which they are not supported.
Extended indicator variables: In the assignment-clause of an UPDATE statement, an expression that is a reference to a single host variable can result in assigning an extended indicator variable value. Assigning an extended indicator variable value of UNASSIGNED has the effect of leaving the target column set to its current values, as if it hadn't be specified in the statement. Assigning an extended indicator variable value of DEFAULT can only be used for columns that have a default value.
If a target column is not updatable, for example an identity column defined as GENERATED ALWAYS, it must be assigned the extended indicator variable value of UNASSIGNED.
An UPDATE statement must not assign all target columns from an extended indicator variable value of UNASSIGNED.
Extended indicator variables and update triggers: If a target column has been assigned an extended indicator variable value of UNASSIGNED, that column is not considered to have been updated.
Extended indicator variables and deferred error checks: When extended indicator variables are enabled, validation that would otherwise be done during statement preparation to recognize an update of a non-updatable column is deferred until the statement is executed.
Notes
Update operation errors: If an update value violates any constraints, or if any other error occurs during the execution of the UPDATE statement and COMMIT(*NONE) was not specified, all changes made during the execution of the statement are backed out. However, other changes in the unit of work made prior to the error are not backed out. If COMMIT(*NONE) is specified, changes are not backed out.
It is possible for an error to occur that makes the state of the cursor unpredictable.
Number of rows updated: When an UPDATE statement completes execution, the number of rows updated is returned in the ROW_COUNT statement information item in the SQL Diagnostics Area (and SQLERRD(3) in the SQLCA). For a description of the SQLCA, see SQLCA (SQL communication area).
For a description of ROW_COUNT, see GET DIAGNOSTICS. For a description of the SQLCA, see SQLCA (SQL communication area).
Locking: Unless appropriate locks already exist, one or more exclusive locks are acquired by the execution of a successful UPDATE statement. Until these locks are released by a commit or rollback operation, the updated rows can only be accessed by:
- The application process that performed the update.
- Another application process using COMMIT(*NONE) or COMMIT(*CHG) through a read-only operation
The locks can prevent other application processes from performing operations on the table. For further information about locking, see the description of the COMMIT, ROLLBACK, and LOCK TABLE statements, and isolation levels in Isolation level. Also, see the Database Programming topic collection.
A maximum of 500 000 000 rows can be updated or changed in any single UPDATE statement when COMMIT(*RR), COMMIT(*ALL), COMMIT(*CS), or COMMIT(*CHG) has been specified. The number of rows changed includes any rows inserted, updated, or deleted under the same commitment definition as a result of a trigger.
REXX: Variables cannot be used in the UPDATE statement within a REXX procedure. Instead, the UPDATE must be the object of a PREPARE and EXECUTE using parameter markers.
Datalinks: If the URL value of a DATALINK column is updated, this is the same as deleting the old DATALINK value then inserting the new one. First, if the old value was linked to a file, that file is unlinked. Then, unless the linkage attributes of the DATALINK value are empty, the specified file is linked to that column.
The comment value of a DATALINK column can be updated without relinking the file by specifying an empty string as the URL path (for example, as the data-location argument of the DLVALUE scalar function or by specifying the new value to be the same as the old value). If a DATALINK column is updated with a null, it is the same as deleting the existing DATALINK value.
An error may occur when attempting to update a DATALINK value if the file server of either the existing value or the new value is no longer registered with the database server
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
- The keyword NONE can be used as a synonym for NC.
- The keyword CHG can be used as a synonym for UR.
- The keyword ALL can be used as a synonym for RS.
Examples
Example 1: Change the job (JOB) of employee number (EMPNO) ‘000290' in the EMPLOYEE table to ‘LABORER'.
UPDATE EMPLOYEE
SET JOB = 'LABORER'
WHERE EMPNO = '000290'Example 2: Increase the project staffing (PRSTAFF) by 1.5 for all projects that department (DEPTNO) ‘D21' is responsible for in the PROJECT table.
UPDATE PROJECT
SET PRSTAFF = PRSTAFF + 1.5
WHERE DEPTNO = 'D21'Example 3: All the employees except the manager of department (WORKDEPT) ‘E21' have been temporarily reassigned. Indicate this by changing their job (JOB) to NULL and their pay (SALARY, BONUS, COMM) values to zero in the EMPLOYEE table.
UPDATE EMPLOYEE
SET JOB=NULL, SALARY=0, BONUS=0, COMM=0
WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'Example 4: In a Java program display the rows from the EMPLOYEE table on the connection context 'ctx' and then, if requested to do so, change the job (JOB) of certain employees to the new job keyed in (NEWJOB).
#sql iterator empIterator implements sqlj.runtime.ForUpdate
with( updateColumns='JOB' )
( … );
empIterator C1;
#sql [ctx] C1 = { SELECT * FROM EMPLOYEE };
#sql { FETCH :C1 INTO … };
while ( !C1.endFetch() ) {
System.out.println( … );
…
if ( condition for updating row ) {
#sql [ctx] { UPDATE EMPLOYEE
SET JOB = :NEWJOB
WHERE CURRENT OF :C1 };
}
#sql { FETCH :C1 INTO … };
}
C1.close();