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
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'
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.
Added in: 2.0
Description
UPDATE now allows a PLAN clause, so users can optimize the operation manually.
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 sort = 'pisang' where ... update Fruit set Fruit.sort = 'pisang' where ... update Fruit F set sort = 'pisang' where ... update Fruit F set F.sort = 'pisang' where ...
No longer possible:
update Fruit F set Fruit.sort = 'pisang' where ...
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:
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:
ROWS can also be used with the SELECT and DELETE statements.