UPDATE

CommentsProcedural SQL (PSQL) Statements

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