update-clause

The optional FOR UPDATE clause identifies the columns that can appear as targets in an assignment clause in a later positioned UPDATE statement.

update-clause

Read syntax diagramSkip visual syntax diagramFOR UPDATE OF,column-name

Each column name must be unqualified and must identify a column of the table or view identified in the first FROM clause of the fullselect. The clause must not be specified if the result table of the fullselect is read-only.

Start of changeIf FOR UPDATE clause is specified with a column-name list, and extended indicators are not enabled, column-name must be an updatable column.End of change

If the FOR UPDATE clause is specified without a column-name list, the implicit list of column names consists of all updatable columns of the table or view that is identified in the first FROM clause of the fullselect.

If a dynamically prepared select-statement does not contain a FOR UPDATE clause, the cursor that is associated with the select statement cannot be referenced in a positioned UPDATE statement.

Start of changeIf a statically prepared select-statement does not contain a FOR UPDATE clause, and its result table is not read-only, an implicit UPDATE clause will result. The implicit column name list is determine as follows:
  • If extended indicators are enabled, all columns of the table or view that is identified in the first FROM clause of the fullselect are included.
  • Otherwise, all updatable columns of the table or view that is identified in the first FROM clause of the fullselect are included.
End of change

The declaration of a cursor referenced in a positioned UPDATE statement need not include an UPDATE clause if the STDSQL(YES) or NOFOR SQL processing option is specified when the program is prepared. For more information, see Positioned updates of columns.

When FOR UPDATE is used, FETCH operations referencing the cursor acquire U or X locks rather than S locks when:

  • The isolation level of the statement is cursor stability.
  • The isolation level of the statement is repeatable read or read stability and the RRULOCK subsystem parameter is set to YES.
  • The isolation level of the statement is repeatable read or read stability and USE AND KEEP EXCLUSIVE LOCKS or USE AND KEEP UPDATE LOCKS is specified in the SQL statement, an X lock or a U lock, respectively, is acquired at fetch time.

No locks are acquired on declared temporary tables. For a discussion of U locks and S locks, see Lock modes and compatibility of locks.

Notes

Start of changeReferencing columns that will be updated:End of change
Start of changeIf a cursor uses FETCH statements to retrieve columns that will be updated later, specify FOR UPDATE OF when you select the columns. Then specify WHERE CURRENT OF in the subsequent UPDATE or DELETE statements. These clauses prevent Db2 from selecting access through an index on the columns that are being updated, which might otherwise cause Db2 to read the same row more than once.

For more information, see Updating previously retrieved data.

End of change