UPDATE

ORDER BY
PLAN
ROWS

Available in: DSQL, ESQL, PSQL

Description: Changes values in a table (or in one or more tables underlying a view). The columns affected are specified in the SET clause; the rows affected may be limited by the WHERE and ROWS clauses.

Syntax:

UPDATE [TRANSACTION name] {tablename | viewname} [alias]
   SET col = newval [, col = newval ...]
   [WHERE {search-conditions | CURRENT OF cursorname}]
   [PLAN plan_items]
   [ORDER BY sort_items]
   [ROWS <m> [TO <n>]]

<m>, <n>  ::=  Any expression evaluating to an integer.
Restrictions

The TRANSACTION directive is only available in ESQL.

WHERE CURRENT OF is only available in ESQL and PSQL.

The PLAN, ORDER BY and ROWS clauses are not available in ESQL.

New in 2.0: No column may be SET more than once in the same UPDATE statement.

ORDER BY

Available in: DSQL, PSQL

Added in: 2.0

Description: UPDATE now allows an ORDER BY clause. This only makes sense in combination with ROWS, but is also valid without it.

PLAN

Available in: DSQL, PSQL

Added in: 2.0

Description: UPDATE now allows a PLAN clause, so users can optimize the operation manually.

ROWS

Available in: DSQL, PSQL

Added in: 2.0

Description: Limits the amount of rows updated to a specified number or range.

Syntax:

ROWS <m> [TO <n>]

<m>, <n>  ::=  Any expression evaluating to an integer.

With a single argument m, the update is limited to the first m rows of the dataset defined by the table or view and the optional WHERE and ORDER BY clauses.

Points to note:

If m > the total number of rows in the dataset, the entire set is updated.
If m = 0, no rows are updated.
If m < 0, an error is raised.

With two arguments m and n, the update is limited to rows m to n inclusively. Row numbers are 1-based.

Points to note when using two arguments:

If m > the total number of rows in the dataset, no rows are updated.
If m lies within the set but n doesn't, the rows from m to the end of the set are updated.
If m < 1 or n < 1, an error is raised.
If n = m-1, no rows are updated.
If n < m-1, an error is raised.

ROWS can also be used with the SELECT and DELETE statements.