UPDATE (DATALAKE) statement

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

Syntax (search-update)

correlation-clause

assignment-clause

Notes:

• ¹ If the order-by-clause is specified, either the offset-clause or fetch-clause must also be specified (SQLSTATE 42601).
• ² 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

Identifies the Iceberg Datalake table object of the update operation.

correlation-clause

Can be used within search-condition or assignment-clause to designate a table. For a description of correlation-clause, see “table-reference” in the description of  “Subselect”.

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. A column must not be specified more than once (SQLSTATE 42701).

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

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.

FROM

Specifies a list of source tables that supply values for assignment to target table columns. The source tables are implicitly inner joined with the target table with the WHERE clause specifying the join condition. The rows in the target table that satisfy the WHERE condition are updated with the values from the source table rows.

When an UPDATE statement specifies a FROM clause:

• The source for the update operation cannot be an analyze_table-expression (that is, the result of a data mining model) or a data-change-table-reference (that is, the result of a nested UPDATE, DELETE, or INSERT statement).

For a description of table-reference, see table-reference.

WHERE

Introduces a condition that indicates what rows are updated. You can omit the clause or give a search condition. If the clause is omitted, all rows of the table are updated.

search-condition

Each column-name in the search condition must name a column of the table. 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 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.

order-by-clause

Specifies the order of the rows for application of the offset-clause and fetch-clause. Specify an order-by-clause to ensure a predictable order for determining the set of rows to be updated based on the offset-clause and fetch-clause. For details on the order-by-clause, see order-by-clause.

offset-clause

Limits the effect of the update by skipping a subset of the qualifying rows. For details on the offset-clause, refer to offset-clause.

fetch-clause

Limits the effect of the update to a subset of the qualifying rows. For details on the fetch-clause, refer to fetch-clause.

Rules

• Assignment: Update values are assigned to columns according to specific assignment rules.
• Validity: Given that only informational constraints are supported for Iceberg Datalake tables, it is the responsibility of the user to ensure that the defined constraints on the table are not violated by the UPDATE statement.
• Security policy: If the identified table 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. The UPDATE statement must not assign all target columns to an extended indicator variable-based value of unassigned (SQLSTATE 22540).

Notes

• 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.
• 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.
• UPDATE operations on a Datalake table are non-transactional: If the statement succeeds, the UPDATE is completely successful and cannot be rolled back by a ROLLBACK or ROLLBACK TO SAVEPOINT statement. If the statement fails, no rows are updated.
• UPDATE operations on a Datalake table are not logged to the transaction log: Rolling forward past a transaction that included an UPDATE operation on a Datalake table does not replay the UPDATE operation.

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' 

• 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: A new location column has been added to the project table. Update the project location with the location of the department handling the project.

    UPDATE PROJECT P 
        SET P.LOCATION = D.LOCATION 
        FROM DEPARTMENT D 
        WHERE P.DEPTNO = D.DEPTNO 

• Example 6: Update the estimated project staffing to the max staffing required for all activities within the project.

     UPDATE PROJECT P 
        SET P.PRSTAFF = S.ACSTAFF 
        FROM (SELECT PROJNO, MAX(ACSTAFF) ACSTAFF FROM PROJACT GROUP BY PROJNO)S 
        WHERE P.PROJNO = S.PROJNO AND 
              P.PROJNAME = 'PAYROLL PROGRAMMING' 

• Example 7: Update an employee's work department to the project department he is assigned to.

     UPDATE EMPLOYEE E 
        SET E.WORKDEPT = P.DEPTNO 
        FROM PROJECT P JOIN EMPPROJACT EP ON P.PROJNO = EP.PROJNO 
        WHERE E.EMPNO = EP.EMPNO AND 
              E.FIRSTNME = 'PHILIP' AND E.LASTNAME = 'SMITH'