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