UPDATE

<< SELECT | FB 2.5 Language Reference | UPDATE OR INSERT >>

UPDATE

Changed SET semantics
COLLATE subclause for
text BLOB columns
ORDER BY
PLAN
Relation alias makes real
name unavailable
RETURNING
ROWS

`UPDATE`

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} [[AS] alias]
   SET col = newval [, col = newval ...]
   [WHERE {search-conditions | CURRENT OF cursorname}]
   [PLAN plan_items]
   [ORDER BY sort_items]
   [ROWS <m> [TO <n>]]
   [RETURNING values [INTO <variables>]]

 <m>, <n> ::= Any expression evaluating to an integer.
 <variables> ::= :varname [, :varname ...]

Restrictions:

The TRANSACTION directive is only available in ESQL.
In a pure DSQL session, WHERE CURRENT OF isn't of much use, since there exists no DSQL statement to create a cursor.
The PLAN, ORDER BY and ROWS clauses are not available in ESQL.
Since v. 2.0, no column may be SET more than once in the same UPDATE statement.
The RETURNING clause is not available in ESQL.
The INTO <variables> subclause is only available in PSQL.
When returning values into the context variable NEW, this name must not be preceded by a colon (":").

Changed `SET` semantics

Changed in: 2.5

Description

In previous Firebird versions, if multiple assignments were done in the SET clause, the new column values would become immediately available to subsequent assigments in the same clause. That is, in a clause like “set a=3, b=a”, b would be set to 3, not to a's old value. This non-standard behaviour has now been corrected.In Firebird 2.5 and up, any assignments in the SET clause will use the old column values.

Example

Given table TSET:

 A B
 ----
 1 0
 2 0

the following statement:

 update tset set a=5, b=a

will change its state to

 A B
 ----
 5 1
 5 2

In versions prior to Firebird 2.5, this would have been:

 A B
 ----
 5 5
 5 5

Retaining the old behaviour: For a limited time, you can keep the old, non-standard behaviour by setting the OldSetClauseSemantics parameter in firebird.conf to 1. This parameter will be deprecated and removed in the future. If set, it will be used for all database connections made through the server.

`COLLATE` subclause for text BLOB columns

Added in: 2.0

Description

COLLATE subclauses are now also supported for text BLOBs.

Example

 update MyTable
   set NameBlobSp = 'Juan'
   where NameBlobBr collate pt_br = 'João'

`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.

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 triggers, 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.

Example

 update Scholars
   set firstname = 'Hugh', lastname = 'Pickering'
   where firstname = 'Henry' and lastname = 'Higgins'
   returning id, old.lastname, new.lastname

Notes:

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.

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.