The UPDATE statement in DB2 for z/OS

UPDATE

The object of UPDATE is a base table or an updatable view. Catalog tables can be updated only in the few columns IBM marks updatable. You need UPDATE privilege on the columns you assign, and SELECT privilege on columns you read in SET or WHERE.

If you omit WHERE on a searched UPDATE, Db2 updates every row. That is legal and almost never what a production program meant. After a successful statement, SQLERRD(3) in the SQLCA is normally the number of rows updated. Zero rows is not an SQL error; you still check SQLCODE 0 and the count. Exclusive locks are taken on updated rows until commit or rollback (uncommitted-read readers can still see the row if you did not update LOBs).

UPDATE SET

SET lists the columns to change. Each target column may appear once. You cannot assign a generated column (except via DEFAULT) or a view column that is an expression. The right-hand side is one of:

1
2
3
4
5
6
7
8
9
10
11
12
UPDATE DSN8C10.EMP
   SET PHONENO = '3565'
 WHERE EMPNO = '000190';
 
UPDATE DSN8C10.EMP
   SET SALARY = SALARY + 100
 WHERE WORKDEPT = 'D11';
 
-- Several columns, one assignment list
UPDATE DSN8C10.EMP
   SET (SALARY, BONUS, COMM) = (NULL, NULL, NULL)
 WHERE EMPNO = '000250';

An expression on the right sees the old column values of that row. SALARY = SALARY + 100 uses the salary before this UPDATE. DEFAULT stores the column’s default (identity, CURRENT TIMESTAMP, a literal default, and so on). NULL is only legal for nullable columns.

Multiple-column assignment (the parenthesized list) is the same idea as assigning each column separately, except a row-fullselect can fill the whole list in one trip. If that fullselect returns no row, each target is set to null (error if any target is NOT NULL). More than one row is an error.

UPDATE from a subquery

A scalar subquery in SET must return one column and at most one row. IBM’s sample updates PROJSIZE from a count of projects:

1
2
3
4
5
UPDATE DSN8C10.EMP
   SET PROJSIZE = (SELECT COUNT(*)
                     FROM DSN8C10.PROJ
                    WHERE DEPTNO = 'E21')
 WHERE WORKDEPT = 'E21';

Correlate when the subquery should follow the current target row. Give the target a correlation name so the subquery can see it. If the same table is the object of both UPDATE and a subquery in WHERE, Db2 fully evaluates that subquery before any row is updated, so you do not chase a moving average mid-statement.

1
2
3
4
5
UPDATE EMP X
   SET SALARY = 1.10 * SALARY
 WHERE SALARY < (SELECT AVG(SALARY)
                   FROM EMP Y
                  WHERE X.JOBCODE = Y.JOBCODE);

UPDATE with joins (z/OS pattern)

Db2 LUW has UPDATE … FROM with a join. Db2 for z/OS does not. On z/OS you either:

• Assign from a correlated scalar subquery or row-fullselect in SET
• Filter with a subquery in WHERE
• Use MERGE, which is the statement built for “match this source to that target”

1
2
3
4
5
6
7
8
-- Copy department name onto an employee work table
UPDATE HR.EMP_WORK E
   SET DEPTNAME = (SELECT D.DEPTNAME
                     FROM HR.DEPARTMENT D
                    WHERE D.DEPTNO = E.WORKDEPT)
 WHERE EXISTS (SELECT 1
                 FROM HR.DEPARTMENT D
                WHERE D.DEPTNO = E.WORKDEPT);

If the subquery can return more than one department row, you get SQLCODE -811. Make the subquery unique (primary key join) or use MERGE with a well-defined ON predicate.

Positioned UPDATE

In COBOL, C, or PL/I you often FETCH a row, show it, then change it. Declare the cursor FOR UPDATE OF the columns you will SET (or accept the default updatable column list). Then:

1
2
3
4
5
6
7
8
9
10
11
12
       EXEC SQL
            DECLARE C1 CURSOR FOR
             SELECT EMPNO, SALARY
               FROM HR.EMPLOYEE
              WHERE WORKDEPT = :HV-DEPT
                FOR UPDATE OF SALARY
       END-EXEC.
       EXEC SQL
            UPDATE HR.EMPLOYEE
               SET SALARY = SALARY + :HV-RAISE
             WHERE CURRENT OF C1
       END-EXEC.

The cursor must be open and positioned. FOR ROW n OF ROWSET updates one row of a rowset cursor; WHERE CURRENT OF on a rowset updates every row in the current rowset. Do not use a read-only cursor (FOR READ ONLY, UNION, DISTINCT, and similar) for positioned UPDATE.

Generated columns

GENERATED ALWAYS columns (identity, row-begin, row-change-timestamp, expression columns) are maintained by Db2. In SET, specify DEFAULT if you must list the column, or leave it out of the assignment list. GENERATED BY DEFAULT may accept a user value. OVERRIDING USER VALUE on related statements tells Db2 to ignore an application value; for UPDATE the usual rule is still DEFAULT for ALWAYS columns. Extended indicator UNASSIGNED means “do not treat this column as updated,” which also affects which UPDATE triggers see the column.

UPDATE temporal data

On a system-period temporal table, an UPDATE of a current row inserts a historical copy into the history table and stamps row-begin / row-end / transaction-start-ID columns. You query history later with FOR SYSTEM_TIME. Do not UPDATE the history table as if it were an ordinary base table.

On an application-period (BUSINESS_TIME) table, FOR PORTION OF BUSINESS_TIME FROM v1 TO v2 (or BETWEEN v1 AND v2) updates only that slice of time. Db2 may split the original row and insert extra rows for the untouched portions, firing INSERT triggers for those automatic inserts and UPDATE triggers for the changed slice. Unique keys that include the period columns can fail if a split would duplicate a key—plan the key design before you use portion updates.

1
2
3
4
5
UPDATE HR.POLICY
   FOR PORTION OF BUSINESS_TIME
       FROM DATE('2024-01-01') TO DATE('2024-07-01')
   SET PREMIUM = PREMIUM * 1.05
 WHERE POLICY_ID = 'P100';

UPDATE XML and LOBs

An XML column can be replaced as a whole (SET XMLCOL = :HV-XML) or changed in place with XMLMODIFY, which applies an XQuery updating expression and returns a new XML value. Validate against an XML schema when your shop requires it. Do not treat XML as VARCHAR concatenation.

1
2
3
4
5
UPDATE HR.EMP_DOCS
   SET RESUME_XML = XMLMODIFY(
         'replace value of node /resume/phone with $p',
         PASSING :HV-PHONE AS "p")
 WHERE EMPNO = :HV-EMPNO;

LOB columns (CLOB, BLOB, DBCLOB) accept host variables, LOB locators, or expressions. IBM’s sample EMP1 table updates a CLOB resume through a user-defined function while also bumping SALARY, keyed by ROWID. Large LOB updates log heavily unless the table space is NOT LOGGED (a design choice with recovery consequences). Host variables need the correct SQL TYPE and, for nulls, indicator variables.

Explain It Like I'm Five

UPDATE is changing the writing on a library card that already exists. SET is which lines you erase and rewrite. WHERE is “only cards for this kid.” If you forget WHERE, you rewrite every card in the drawer. A cursor UPDATE is “change the card I am holding in my hand.” A subquery is looking at another drawer to decide the new number. Temporal tables keep a photocopy of the old card in a history box before you write the new one.

Exercises

1. Write a searched UPDATE that adds 3% to SALARY for JOB = 'ANALYST' in department D11.
2. Rewrite three separate SET assignments for SALARY, BONUS, and COMM as one parenthesized multiple-column assignment.
3. Write a SET clause that copies DEPTNAME from DEPARTMENT using a scalar subquery. What SQLCODE appears if two departments share the same DEPTNO?
4. Explain why UPDATE EMP SET ID = ID + 1 is a bad idea on a GENERATED ALWAYS identity column.
5. Outline the COBOL steps for a positioned UPDATE: DECLARE, OPEN, FETCH, UPDATE WHERE CURRENT OF, CLOSE.