FirebirdSQL logo

OVERRIDING

The OVERRIDING clause controls the behaviour of an identity column for this statement only.

OVERRIDING SYSTEM VALUE

The user-provided value for the identity column is used, and no value is generated using the identity.In other words, for this insert, the identity will behave as if it is GENERATED BY DEFAULT.This option can only be specified for tables with a GENERATED ALWAYS identity column.

This can be useful when merging or importing data from another source.After such an insert, it may be necessary to change the next value of the identity sequence using ALTER TABLE to prevent subsequent inserts from generating colliding identity values.

OVERRIDING USER VALUE

The user-provided value for the identity column is ignored, and the column value is generated using the identity.In other words, for this insert, the identity will behave as if it is GENERATED ALWAYS, while allowing the identity column in the column-list.This option can be specified for both types of identity columns.

It is usually simpler to leave out the identity column to achieve the same effect.

Examples of OVERRIDING
-- for ALWAYS
-- value 11 is used anyway
insert into objects_always (id, name)
  OVERRIDING SYSTEM VALUE values (11, 'Laptop');

-- for both ALWAYS and BY DEFAULT
-- value 12 is not used
insert into objects_default (id, name)
  OVERRIDING USER VALUE values (12, 'Laptop');

The RETURNING Clause

An INSERT statement may optionally include a RETURNING clause to return values from the inserted rows.The clause, if present, need not contain all columns referenced in the insert statement and may also contain other columns or expressions.The returned values reflect any changes that may have been made in BEFORE INSERT triggers.

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

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

The optional INTO sub-clause is only valid in PSQL.

Note
Caveats for updatable views

The values reported by RETURNING for updatable views may be incorrect if the view is made updatable through triggers.See [fblangref50-ddl-view-updatable-returning].

Important
Multiple INSERTs

In DSQL, an INSERT …​ VALUES (…​) RETURNING or INSERT …​ DEFAULT VALUES RETURNING returns only one row, and a INSERT …​ SELECT …​ RETURNING can return zero or more rows.

In PSQL, if the RETURNING clause is specified and more than one row is inserted by the INSERT statement, the statement fails and a “multiple rows in singleton select” error is returned.This behaviour may change in future Firebird versions.

Examples
INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES ('Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING lastname, fullname, id;

INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES (
  'Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING *;

INSERT INTO Dumbbells (firstname, lastname, iq)
  SELECT fname, lname, iq
FROM Friends
  ORDER BY iq ROWS 1
  RETURNING id, firstname, iq
INTO :id, :fname, :iq;
Note
  • In DSQL, an INSERT …​ VALUES (…​) RETURNING always returns exactly one row.This behaviour may change in a future Firebird version.

  • In DSQL, an INSERT …​ DEFAULT VALUES RETURNING always returns exactly one row.

  • In DSQL, an INSERT …​ SELECT …​ RETURNING returns zero or more rows.

  • In PSQL, if multiple rows are returned, the statement fails with a “multiple rows in singleton select” error.This behaviour may change in a future Firebird version.

  • In PSQL, if no row was inserted, nothing is returned, and the target variables keep their existing values.

Inserting into BLOB columns

Inserting into BLOB columns is only possible under the following circumstances:

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

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

    Note

    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. You are using the “INSERT …​ SELECT” form and one or more columns in the result set are BLOBs.

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.