UPDATE

Table of Contents

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

Tip

Find a more recent version at Firebird 5.0 Language Reference: 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>]]

<m>, <n>  ::=  Any expression evaluating to an integer.
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.

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

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

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.