6.3UPDATE

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 6.16Arguments for the UPDATE Statement Parameters
ArgumentDescription

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.

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

6.3.2The SET Clause

In the SET clause, the assignment expressions, containing the columns with the values to be set, are separated by commas. In an assignment expression, column names are on the left and the values or expressions to assign are on the right. A column may be assigned only once in the SET clause.

A column name can be used in expressions on the right. The old value of the column will always be used in these right-side values, even if the column was already assigned a new value earlier in the SET clause.

Using the expression DEFAULT will set the column to its default value (either NULL or the value specified on the DEFAULT clause of the column definition). For an identity column, specifying DEFAULT will generate a new identity value. It is possible to update calculated columns in the SET clause if and only if the assigned value is DEFAULT.

It is not possible to assign DEFAULT as a parameter value.

Here is an exampleData in the TSET table:

  |A B
  |---
  |1 0
  |2 0

The statement:

  |UPDATE tset SET a = 5, b = a;

will change the values to:

  |A B
  |---
  |5 1
  |5 2

Notice that the old values (1 and 2) are used to update the b column even after the column was assigned a new value (5).

6.3.3The WHERE Clause

The WHERE clause sets the conditions that limit the set of records for a searched update.

In PSQL, if a named cursor is being used for updating a set, using the WHERE CURRENT OF clause, the action is limited to the row where the cursor is currently positioned. This is a positioned update.

To be able to use the WHERE CURRENT OF clause in DSQL, the cursor name needs to be set on the statement handle before executing the statement.

Examples

   |UPDATE People
   |  SET firstname = 'Boris'
   |  WHERE lastname = 'Johnson';
   | 
   |UPDATE employee e
   |  SET salary = salary * 1.05
   |  WHERE EXISTS(
   |         SELECT *
   |           FROM employee_project ep
   |           WHERE e.emp_no = ep.emp_no);
   | 
   |UPDATE addresses
   |  SET city = 'Saint Petersburg', citycode = 'PET'
   |  WHERE city = 'Leningrad'
   | 
   |UPDATE employees
   |  SET salary = 2.5 * salary
   |  WHERE title = 'CEO'

For string literals with which the parser needs help to interpret the character set of the data, the introducer syntax may be used. The string literal is preceded by the character set name, prefixed with an underscore character:

  |-- notice the '_' prefix
  | 
  |UPDATE People
  |SET name = _ISO8859_1 'Hans-Jörg Schäfer'
  |WHERE id = 53662;

6.3.4The ORDER BY and ROWS Clauses

The ORDER BY and ROWS clauses make sense only when used together. However, they can be used separately.

If ROWS has one argument, m, the rows to be updated will be limited to the first m rows.

Points to note
  • If m > the number of rows being processed, the entire set of rows is updated

  • If m = 0, no rows are updated

  • If m < 0, an error occurs and the update fails

If two arguments are used, m and n, ROWS limits the rows being updated to rows from m to n inclusively. Both arguments are integers and start from 1.

Points to note
  • If m > the number of rows being processed, no rows are updated

  • If n > the number of rows, rows from m to the end of the set are updated

  • If m < 1 or n < 1, an error occurs and the update fails

  • If n = m - 1, no rows are updated

  • If n < m -1, an error occurs and the update fails

ROWS Example

  |UPDATE employees
  |SET salary = salary + 50
  |ORDER BY salary ASC
  |ROWS 20;

6.3.5The SKIP LOCKED Clause

When the SKIP LOCKED clause is specified, records locked by a different transaction are skipped by the statement and are not updated.

When a ROWS clause is specified, the skip locked check is performed after skipping the requested number of rows specified, and before counting the number of rows to update.

6.3.6The RETURNING Clause

An UPDATE statement may include RETURNING to return some values from the updated rows. RETURNING may include data from any column of the row, not only the columns that are updated by the statement. It can include literals or expressions not associated with columns, if there is a need for that.

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

When the RETURNING set contains data from the current row, the returned values report changes made in the BEFORE UPDATE triggers, but not those made in AFTER UPDATE triggers.

The context variables OLD.fieldname and NEW.fieldname can be used as column names. If OLD. or NEW. is not specified, or if the table name (target) is specified instead, the column values returned are the NEW. ones.

The syntax of the returning_list is similar to the column list of a SELECT clause. It is possible to reference all columns using *, or table_name.*, NEW.* and/or OLD.*.

In DSQL, a positioned update statement (WHERE CURRENT OF …​) with RETURNING always returns a single row, a normal update statement can return zero or more rows. The update is executed to completion before rows are returned. In PSQL, attempts to execute an UPDATE …​ RETURNING that affects multiple rows will result in the error multiple rows in singleton select. This behaviour may change in a future Firebird version.

6.3.6.1The INTO Sub-clause

In PSQL, the INTO clause can be used to pass the returning values to local variables. It is not available in DSQL. If no records are updated, nothing is returned and variables specified in RETURNING will keep their previous values.

6.3.6.2RETURNING Example (DSQL)

  |UPDATE Scholars
  |SET firstname = 'Hugh', lastname = 'Pickering'
  |WHERE firstname = 'Henry' and lastname = 'Higgins'
  |RETURNING id, old.lastname, new.lastname;

6.3.7Updating BLOB columns

Updating a BLOB column always replaces the entire contents. Even the BLOB ID, the handle that is stored directly in the column, is changed. BLOBs can be updated if:

  1. The client application has made special provisions for this operation, using the Firebird API. In this case, the modus operandi is application-specific and outside the scope of this manual.

  2. The new value is a string literal of no more than 65,533 bytes (64KB - 3).

    A limit, in characters, is calculated at run-time for strings that are in multi-byte character sets, to avoid overrunning the bytes limit. For example, for a UTF8 string (max. 4 bytes/character), the run-time limit is likely to be about (floor(65533/4)) = 16383 characters.

  3. The source is itself a BLOB column or, more generally, an expression that returns a BLOB.

  4. You use the INSERT CURSOR statement (ESQL only).