update-clause

Start of changeThe optional FOR UPDATE clause identifies the columns that can appear as targets in an assignment clause in a later positioned UPDATE statement.End of change

update-clause

Read syntax diagram
>>-FOR UPDATE--+---------------------+-------------------------><
               |     .-,-----------. |   
               |     V             | |   
               '-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 change If FOR UPDATE clause is specified with a column-name list column-name must be an updatable column.End of change

Start of changeIf 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.End of change

Start of changeIf 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.End of change

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 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.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 on the subject, 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 field U LOCK FOR RR/RS on installation panel DSNTIPI is set to get U locks.
  • 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 Managing DB2 Performance.