UPDATE

Table of Contents

Using an alias
The SET Clause
The WHERE Clause
The ORDER BY and ROWS Clauses
The RETURNING Clause
Updating BLOB columns

Used for: Modifying rows in tables and views

Available in: DSQL, ESQL, PSQL

Syntax:

   UPDATE target [[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 <returning_list> [INTO <variables>]]

<returning_list>     ::=  ret_value [, ret_value ...]
<variables>  ::=  :varname [, :varname ...]

Table 6.12. 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 or alias of a column in the table or view
newval	New value for a column that is to be updated in the table or view by the statement
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
ret_value	A value to be returned in the RETURNING clause
varname	Name of a PSQL local variable

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

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

Not possible:

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

The SET Clause

In the SET clause, the assignment phrases, containing the columns with the values to be set, are separated by commas. In an assignment phrase, column names are on the left and the values or expressions containing the assignment values are on the right. A column may be included 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.

Here is an example: Data 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).

Note

It was not always like that. Before version 2.5, columns got their new values immediately upon assignment. It was non-standard behaviour that was fixed in version 2.5.

To maintain compatibility with legacy code, the configuration file firebird.conf includes the parameter OldSetClauseSemantics, that can be set True (1) to restore the old, bad behaviour. It is a temporary measure—the parameter will be removed in future.

The WHERE Clause

Table of Contents

The “Unstable Cursor” Problem

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.

	Note
	The WHERE CURRENT OF clause is available only in PSQL, since there is no statement for creating and manipulating an explicit cursor in DSQL. Searched updates are also available in PSQL, of course.

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

The “Unstable Cursor” Problem

In Firebird, up to and including this version, it is necessary to be aware of an implementation fault that affects updates when the WHERE conditions use the IN (select-expr) and the select-expr is of the form SELECT FIRST n or SELECT ... ROWS. For example

UPDATE T
  SET ...
  WHERE ID IN (SELECT FIRST 1 ID FROM T)

known affectionately as the “infinite update loop”, will continuously update rows, over and over, and give the impression that the server has hung.

Quirks like this can affect any data-changing DML operations, most often when the selection conditions involve a subquery. Cases have been reported where sort order interferes with expectations, without involving a subquery. It happens because, in the execution layers, instead of establishing a stable “target set” and then executing the data changes to each set member, DML statements use implicit cursors for performing the operations on whatever row currently meets the conditions, without knowledge of whether that row formerly failed the condition or was updated already. Thus, using a simple example pattern:

UPDATE T SET <fields> = <values>
  WHERE <conditions>

the execution works as:

FOR SELECT <values> FROM T
  WHERE <conditions>
  INTO <tmp_vars> AS CURSOR <cursor>
  DO
    UPDATE T SET <fields> = <tmp_vars>
    WHERE CURRENT OF <cursor>

Firebird's implementation does not accord with the SQL standards, which require that a stable set be established before any data are changed. Versions of Firebird from V.3 onward will comply with the standard.

The 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

The RETURNING Clause

Table of Contents

The INTO Sub-clause

An UPDATE statement involving at most one row may include RETURNING in order to return some values from the row being updated. RETURNING may include data from any row, not necessarily the one that is currently being updated. It can include literals not associated with columns, if there is a need for that.

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, the column values returned are the NEW. ones.

In DSQL, a statement with RETURNING always returns a single row. If the statement updates no records, the returned values contain NULL. This behaviour may change in future Firebird versions.

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

Note

When a value is returned and assigned to a NEW context variable, it is not valid to use a colon prefix on it. For example, this is invalid:

...
into :var1, :var2, :new.id

and this is valid:

...
into :var1, :var2, new.id

RETURNING Example (DSQL):

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

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

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.
The new value is a text string of at most 32767 bytes. Please notice: if the value is not a string literal, beware of concatenations, as these may exceed the maximum length.
The source is itself a BLOB column or, more generally, an expression that returns a BLOB.
You use the INSERT CURSOR statement (ESQL only).