FirebirdSQL logo

UPDATE

Updates existing rows in tables and updatable views

Syntax
UPDATE target [[AS] alias]
  SET col_name = <upd_value> [, col_name = <upd_value> ...]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [SKIP LOCKED]
  [RETURNING <returning_list> [INTO <variables>]]

<upd_value> ::= <value-expression> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value-expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value-expression> ::=
    <literal>
  | <context-variable>
  | any other expression returning a single
    value of a Firebird data type or NULL

<variables> ::= [:]varname [, [:]varname ...]
Table 1. Arguments for the UPDATE Statement Parameters
Argument Description

target

The name of the table or view where the records are updated

alias

Alias for the table or view

col_name

Name or alias of a column in the table or view

value-expression

Expression for the new value for a column that is to be updated in the table or view by the statement, or a value to be returned

search-conditions

A search condition limiting the set of the rows to be updated

cursorname

The name of the cursor through which the row(s) to be updated are positioned

plan_items

Clauses in the query plan

sort_items

Columns listed in an ORDER BY clause

m, n

Integer expressions for limiting the number of rows to be updated

return_expression

A value to be returned in the RETURNING clause

literal

A literal

context-variable

Context variable

varname

Name of a PSQL local variable

The UPDATE statement changes values in a table or in one or more of the tables that underlie a view.The columns affected are specified in the SET clause.The rows affected may be limited by the WHERE and ROWS clauses.If neither WHERE nor ROWS is present, all records in the table will be updated.

Using an alias

If you assign an alias to a table or a view, the alias must be used when specifying columns and also in any column references included in other clauses.

Example

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

Not possible:

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