SQL Reference

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.

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 table or view where rows are to be updated
• UPDATE privilege on each of the columns to be updated.
• CONTROL privilege on the table or view where rows are to be updated
• SYSADM or DBADM authority.
• If a row-fullselect is included in the assignment, at least one of the following for each referenced table or view:
  • SELECT privilege
  • CONTROL privilege
  • SYSADM or DBADM authority.

For each table or view 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
• SYSADM or DBADM authority.

When the package is precompiled with SQL92 rules ¹⁰⁷ and the searched form of an UPDATE includes a reference to a column of the table or view 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:

• SELECT privilege
• CONTROL privilege
• SYSADM or DBADM authority.

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

Syntax

Searched UPDATE:

>>-UPDATE----+-table-name-------------------+------------------->
             +-view-name--------------------+
             '-ONLY--(--+-table-name-+---)--'
                        '-view-name--'
 
>-----+---------------------------+----------------------------->
      | .-AS-.                    |
      '-+----+--correlation-name--'
 
>-----SET--| assignment-clause |-------------------------------->
 
>-----+--------------------------+-----------------------------><
      '-WHERE--search-condition--'
 

Positioned UPDATE:

>>-UPDATE----+-table-name-------------------+------------------->
             +-view-name--------------------+
             '-ONLY--(--+-table-name-+---)--'
                        '-view-name--'
 
>-----SET--| assignment-clause |-------------------------------->
 
>-----WHERE CURRENT OF--cursor-name----------------------------><
 
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:

1. The number of expressions, NULLs and DEFAULTs must match the number of column-names.

2. The number of columns in the select list must match the number of column-names.

Description

table-name  or  view-name

Is the name of the table or view to be updated. The name must identify a table or view 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 summary table, read-only view, or a nickname. (For an explanation of read-only views, see CREATE VIEW. For an explanation of updatable catalog views, see Appendix D, Catalog Views.)

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 or view in the FROM clause without using ONLY.

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.

AS

Optional keyword to introduce the correlation-name.

correlation-name

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

SET

Introduces the assignment of values to column names.

assignment-clause

column-name

Identifies a column to be updated. The column-name must identify an updatable column of the specified table or view. ¹⁰⁸ 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 an attribute-name (SQLSTATE 42701).

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 can not include a column 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.

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, then the value is set to the default defined for the column (see default-clause in ALTER TABLE).
• If the column was defined without specifying the WITH DEFAULT clause, the GENERATED clause, or the NOT NULL clause, then the value used is NULL.
• 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).

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

row-fullselect

A fullselect that returns a single row with the number of columns corresponding to the number of column-names specified for assignment. The values are assigned to each corresponding column-name. If the result of the row-fullselect is no rows, then null values are assigned.

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

search-condition

Is any search condition as described in Language Elements. 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 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 as explained in DECLARE CURSOR. The DECLARE CURSOR statement must precede the UPDATE statement in the program.

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.

This form of UPDATE cannot be used if the target of the update is a view that includes an OLAP function in the select list of the fullselect that defines the view (SQLSTATE 42828).

Rules

• Assignment:  Update values are assigned to columns under the assignment rules described in "Language Elements".
• 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.

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.
• When an UPDATE statement completes execution, the value of SQLERRD(3) in the SQLCA is the number of rows updated. The SQLERRD(5) field contains the number of rows inserted, deleted, or updated by all activated triggers. For a description of the SQLCA, see Appendix B, SQL Communications (SQLCA).
• 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.
• 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 (SQLSTATE 55022).

• 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. ¹⁰⁹
• 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.
• See INSERT for more information on consuming values of a generated sequence for an identity column.
• See INSERT for more information on exceeding the maximum value for an identity column.

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'
  

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

¹⁰⁷

The package used to process the statement is precompiled using option LANGLEVEL with value SQL92E or MIA.

¹⁰⁸

A column of a partitioning key is not updatable (SQLSTATE 42997). The row of data must be deleted and inserted to change columns in a partitioning key.

¹⁰⁹

There is no casting of the previous value to the source type prior to the computation.