UPDATE

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.

ORDER BY

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.

Relation alias makes real name unavailable

Changed in: 2.0

Description: If you give a table or view an alias in a Firebird 2.0 or above statement, you must use the alias, not the table name, if you want to qualify fields from that relation.

Examples:

Correct usage:

update Fruit set soort = 'pisang' where ...

update Fruit set Fruit.soort = 'pisang' where ...

update Fruit F set soort = 'pisang' where ...

update Fruit F set F.soort = 'pisang' where ...

No longer possible:

update Fruit F set Fruit.soort = 'pisang' where ...

RETURNING

Available in: DSQL, PSQL

Added in: 2.1

Description: An UPDATE statement modifying at most one row may optionally include a RETURNING clause in order to return values from the updated row. The clause, if present, need not contain all the modified columns
and may also contain other columns or expressions. The returned values reflect any changes that may have been made in BEFORE tiggers, but not those in AFTER triggers. OLD.fieldname and NEW.fieldname may both be used in the list of columns to return; for field names not preceded by either of these, the new value is returned.

In DSQL, a statement with a RETURNING clause always returns exactly one row. If no record was actually updated, the fields in this row are all NULL. This behaviour may change in a later version of Firebird. In PSQL, if no row was updated, nothing is returned, and the receiving
variables keep their existing values.

ROWS

Available in: DSQL, PSQL

Added in: 2.0

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