DB2 Version 9.7 for Linux, UNIX, and Windows

UPDATE statement

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:

UPDATE privilege on the target table, view, or nickname
UPDATE privilege on each of the columns that are to be updated
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 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:

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 hand 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:

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------------------+   
           +-nickname-------------------+   
           +-ONLY--(--+-table-name-+--)-+   
           |          '-view-name--'    |   
           '-(--fullselect--)-----------'   

>--+------------------------+--+---------------------+---------->
   '-| correlation-clause |-'  '-| include-columns |-'   

>--SET--| assignment-clause |--+-------------------------+------>
                               '-WHERE--search-condition-'   

>--+--------------+--------------------------------------------><
   '-WITH--+-RR-+-'   
           +-RS-+     
           +-CS-+     
           '-UR-'

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 a table, view, or nickname described in the catalog, but not 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).

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.

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 on 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 prior to 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.

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 above statements. This example combines the effects of both of the above 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'