Skip to content

Non‐standard clauses

Rene Saarsoo edited this page Jan 21, 2024 · 2 revisions

Non-standard additional select clauses in various dialects.

DB2:

FOR UPDATE [OF column_name [, ...]]

FOR {READ | FETCH} ONLY

OPTIMIZE FOR integer {ROW | ROWS}

FOR {RR [lock_clause] | RS [lock_clause] | CS | UR}

lock_clause:
    USE AND KEEP {SHARE | UPDATE | EXCLUSIVE} LOCKS

{ WAIT FOR OUTCOME | SKIP LOCKED DATA }

DB2i:

FOR UPDATE [OF column_name [, ...]]

FOR READ ONLY

OPTIMIZE FOR {integer | ALL} {ROW | ROWS}

FOR {NC | UR | CS [KEEP LOCKS] | RS [lock_clause] | RR [lock_clause]}

lock_clause:
    USE AND KEEP EXCLUSIVE LOCKS

{ WAIT FOR OUTCOME | SKIP LOCKED DATA | USE CURRENTLY COMMITTED }

Oracle:

FOR UPDATE
  [OF identifier {"," ... }]
  [NOWAIT | WAIT integer | SKIP LOCKED]

Postgres:

FOR {UPDATE | NO KEY UPDATE | SHARE | KEY SHARE}
  [OF table_name [, ...]] [NOWAIT | SKIP LOCKED] [...]

Transact-SQL:

FOR {BROWSE | XML | JSON} ...

INTO new_table

OPTION ( <query_hint> [, ...] )

TODO: Check all other dialects.

Clone this wiki locally