Updating an existing record in a table or, if it does not exist, inserting it

    Available in

    DSQL, PSQL

    Syntax

    Description

    6.4.1. The RETURNING clause

    The optional RETURNING clause, if present, need not contain all the columns mentioned in the statement and may also contain other columns or expressions. The returned values reflect any changes that may have been made in BEFORE triggers, but not those in AFTER triggers. OLD.*fieldname* and may both be used in the list of columns to return; for field names not preceded by either of these, the new value is returned.

    In DSQL, a statement with a RETURNING clause always returns exactly one row. If a RETURNING clause is present and more than one matching record is found, an error is raised. This behaviour may change in a later version of Firebird.

    6.4.2. Example of UPDATE OR INSERT

    Modifying data in a table, using UPDATE OR INSERT in a PSQL module. The return value is passed to a local variable, whose colon prefix is optional.

    1. UPDATE OR INSERT INTO Cows (Name, Number, Location)
    2.   VALUES ('Suzy Creamcheese', 3278823, 'Green Pastures')
    3.   RETURNING rec_id into :id;