FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

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:

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

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

UPDATE OR INSERT

Updates existing rows in a table or updatable view, or — if it does not exist — inserts it

Syntax
UPDATE OR INSERT INTO
  target [(<column_list>)]
  [<override_opt>]
  VALUES (<value_list>)
  [MATCHING (<column_list>)]
  [ORDER BY <ordering-list>]
  [ROWS <m> [TO <n>]]
  [RETURNING <returning_list> [INTO <variables>]]

<column_list> ::= col_name  [, col_name ...]

<override_opt> ::=
  OVERRIDING {USER | SYSTEM} VALUE

<value_list> ::= <ins_value> [, <ins_value> ...]

<ins_value> ::= <value> | 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 OR INSERT Statement Parameters
Argument Description

target

The name of the table or view where the record(s) is to be updated or a new record inserted

col_name

Name of a column in the table or view

value-expression

An expression whose value is to be used for inserting or updating the table, or returning a value

return_expression

An expression returned in the RETURNING clause

varname

Variable name — PSQL only

UPDATE OR INSERT inserts a new record or updates one or more existing records.The action taken depends on the values provided for the columns in the MATCHING clause (or, if the latter is absent, in the primary key).If there are records found matching those values, they are updated.If not, a new record is inserted.A match only counts if all the columns in the MATCHING clause or primary key columns are equal.Matching is done with the IS NOT DISTINCT operator, so one NULL matches another.

Note
Restrictions

  • If the table has no primary key, the MATCHING clause is mandatory.

  • In the MATCHING list as well as in the update/insert column list, each column name may occur only once.

  • The “INTO <variables>” subclause is only available in PSQL.

  • When values are returned into the context variable NEW, this name must not be preceded by a colon (“:”).