FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

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.

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

UPDATE OR INSERT INTO Cows (Name, Number, Location)
  VALUES ('Suzy Creamcheese', 3278823, 'Green Pastures')
  MATCHING (Number)
  RETURNING old.*, new.*;

DELETE

Deletes rows from a table or updatable view

Syntax
DELETE
  FROM target [[AS] alias]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [SKIP LOCKED]
  [RETURNING <returning_list> [INTO <variables>]]

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value-expression>
  | [target.]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 DELETE Statement Parameters
Argument Description

target

The name of the table or view from which the records are to be deleted

alias

Alias for the target table or view

search-conditions

Search condition limiting the set of rows being targeted for deletion

cursorname

The name of the cursor in which current record is positioned for deletion

plan_items

Query plan clause

sort_items

ORDER BY clause

m, n

Integer expressions for limiting the number of rows being deleted

return_expression

An expression to be returned in the RETURNING clause

value-expression

An expression whose value is used for returning

varname

Name of a PSQL variable

DELETE removes rows from a database table or from one or more of the tables that underlie a view.WHERE and ROWS clauses can limit the number of rows deleted.If neither WHERE nor ROWS is present, DELETE removes all the rows in the relation.

Aliases

If an alias is specified for the target table or view, it must be used to qualify all field name references in the DELETE statement.

Examples

Supported usage:

delete from Cities where name starting 'Alex';

delete from Cities where Cities.name starting 'Alex';

delete from Cities C where name starting 'Alex';

delete from Cities C where C.name starting 'Alex';

Not possible:

delete from Cities C where Cities.name starting 'Alex';

WHERE

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

In PSQL, if a named cursor is being used for deleting 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 delete.

Note

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
DELETE FROM People
  WHERE firstname <> 'Boris' AND lastname <> 'Johnson';

DELETE FROM employee e
  WHERE NOT EXISTS(
    SELECT *
    FROM employee_project ep
     WHERE e.emp_no = ep.emp_no);

DELETE FROM Cities
  WHERE CURRENT OF Cur_Cities;  -- ESQL and PSQL only

PLAN

A PLAN clause allows the user to optimize the operation manually.

Example
DELETE FROM Submissions
  WHERE date_entered < '1-Jan-2002'
  PLAN (Submissions INDEX ix_subm_date);

ORDER BY and ROWS

The ORDER BY clause orders the set before the actual deletion takes place.It only makes sense in combination with ROWS, but is also valid without it.

The ROWS clause limits the number of rows being deleted.Integer literals or any integer expressions can be used for the arguments m and n.

If ROWS has one argument, m, the rows to be deleted 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 deleted

  • If m = 0, no rows are deleted

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

If two arguments are used, m and n, ROWS limits the rows being deleted 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 deleted

  • If m > 0 and <= the number of rows in the set and n is outside these values, rows from m to the end of the set are deleted

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

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

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

Examples

Deleting the oldest purchase:

DELETE FROM Purchases
  ORDER BY date ROWS 1;

Deleting the highest custno(s):

DELETE FROM Sales
  ORDER BY custno DESC ROWS 1 to 10;

Deleting all sales, ORDER BY clause pointless:

DELETE FROM Sales
  ORDER BY custno DESC;

Deleting one record starting from the end, i.e. from Z…​:

DELETE FROM popgroups
  ORDER BY name DESC ROWS 1;

Deleting the five oldest groups:

DELETE FROM popgroups
  ORDER BY formed ROWS 5;

No sorting (ORDER BY) is specified so 8 found records, starting from the fifth one, will be deleted:

DELETE FROM popgroups
  ROWS 5 TO 12;

SKIP LOCKED

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

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

RETURNING

A DELETE statement may optionally include a RETURNING clause to return values from the deleted rows.The clause, if present, need not contain all the relation’s columns and may also contain other columns or expressions.

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

Note

  • In DSQL, a positioned delete statement (WHERE CURRENT OF …​) with RETURNING always returns a singleton, never a multi-row set.If no records is deleted, the returned columns contain NULL.

  • A normal DELETE statement can return zero or more rows;the deletion is executed to completion before rows are returned.

  • In PSQL, if a RETURNING clause is present and more than one matching record is found, an error “multiple rows in singleton select” is raised.This behaviour may change in a future Firebird version.

  • The INTO clause is available only in PSQL

    • If no row is deleted, nothing is returned and the target variables keep their values
Examples
DELETE FROM Scholars
  WHERE firstname = 'Henry' and lastname = 'Higgins'
  RETURNING lastname, fullname, id;

DELETE FROM Scholars
  WHERE firstname = 'Henry' and lastname = 'Higgins'
  RETURNING *;

DELETE FROM Dumbbells
  ORDER BY iq DESC
  ROWS 1
  RETURNING lastname, iq into :lname, :iq;

MERGE

Merges data from a source set into a target table or updatable view

Syntax
MERGE INTO target [[AS] target_alias]
  USING <table-reference>
  ON <join_condition>
  <merge_when> [<merge_when> ...]
  [PLAN <plan-expr>]
  [ORDER BY <ordering-list>]
  [RETURNING <returning_list> [INTO <variables>]]

<merge_when> ::=
    <merge_when_matched>
  | <merge_when_not_matched_target>
  | <merge_when_not_matched_source>

<merge_when_matched> ::=
  WHEN MATCHED [AND <condition>] THEN
  { UPDATE SET <assignment-list>
  | DELETE }

<merge_when_not_matched_target> ::=
  WHEN NOT MATCHED [BY TARGET] [AND <condition>] THEN
  INSERT [( <column_list> )] [<override_opt>]
  VALUES ( <value_list> )

<merge_when_not_matched_source> ::=
  WHEN NOT MATCHED BY SOURCE [ AND <condition> ] THEN
  { UPDATE SET <assignment-list>
  | DELETE }

<table-reference> ::= <table-primary> | <joined-table>

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<assignment_list ::=
  col_name = <m_value> [, <col_name> = <m_value> ...]]

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

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

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

<m_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 MERGE Statement Parameters
Argument Description

target

Name of target relation (table or updatable view)

table-reference

Data source.It can be a table, a view, a stored procedure, a derived table or a parenthesized joined table

target_alias

Alias for the target relation (table or updatable view)

join_conditions

The (ON) condition(s) for matching the source records with those in the target

condition

Additional test condition in WHEN MATCHED or WHEN NOT MATCHED clause

col_name

Name of a column in the target relation

value-expression

The value assigned to a column in the target table.This expression may be a literal value, a PSQL variable, a column from the source, or a compatible context variable

return_expression

The expression to be returned in the RETURNING clauseCan be a column reference to source or target, or a column reference of the NEW or OLD context of the target, or a value.

ret_alias

Alias for the value expression in the RETURNING clause

varname

Name of a PSQL local variable

The MERGE statement merges records from a source <table-reference> into a target table or updatable view.The source may be a table, view or “anything you can SELECT from” in general.Each source record will be used to update one or more target records, insert a new record in the target table, delete a record from the target table or do nothing.

The action taken depends on the supplied join condition, the WHEN clause(s), and the — optional — condition in the WHEN clause.The join condition and condition in the WHEN will typically contain a comparison of fields in the source and target relations.

Multiple WHEN MATCHED and WHEN NOT MATCHED clauses are allowed.For each row in the source, the WHEN clauses are checked in the order they are specified in the statement.If the condition in the WHEN clause does not evaluate to true, the clause is skipped, and the next clause will be checked.This will be done until the condition for a WHEN clause evaluates to true, or a WHEN clauses without condition matches, or there are no more WHEN clauses.If a matching clause is found, the action associated with the clause is executed.For each row in the source, at most one action is executed.If the WHEN MATCHED clause is present, and several records match a single record in the target table, an error is raised.

Contrary to the other WHEN clauses, the WHEN NOT MATCHED BY SOURCE clauses evaluates records in the target which match no record in source.

Warning

At least one WHEN clause must be present.

WHEN NOT MATCHED is evaluated from the source viewpoint, that is, the table or set specified in USING.It has to work this way because if the source record does not match a target record, INSERT is executed.Of course, if there is a target record which does not match a source record, nothing is done.

Currently, in PSQL, the ROW_COUNT variable returns the value 1, even if more than one record is modified or inserted.For details and progress, refer to firebird#4722.

The ORDER BY Clause

The ORDER BY can be used to influence the order in which rows are evaluated.The primary use case is when combined with RETURNING, to influence the order rows are returned.

The RETURNING Clause

A MERGE statement can contain a RETURNING clause to return rows added, modified or removed.The merge is executed to completion before rows are returned.The RETURNING clause can contain any columns from the target table (or updatable view), as well as other columns (eg from the source) and expressions.

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

In PSQL, If a RETURNING clause is present and more than one matching record is found, an error “multiple rows in singleton select” is raised.This behaviour may change in a future Firebird version.

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

Column names can be qualified by the OLD or NEW prefix to define exactly what value to return: before or after modification. The returned values include the changes made by BEFORE triggers.

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

For the UPDATE or INSERT action, unqualified column names, or those qualified by the target table name or alias will behave as if qualified by NEW, while for the DELETE action as if qualified by OLD.

The following example modifies the previous example to affect one line, and adds a RETURNING clause to return the old and new quantity of goods, and the difference between those values.

Using MERGE with a RETURNING clause
MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM(SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
  JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
  AND SL.ID_PRODUCT =: ID_PRODUCT
  GROUP BY 1
) AS SRC (ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE
RETURNING OLD.QUANTITY, NEW.QUANTITY, SRC.QUANTITY
INTO : OLD_QUANTITY, :NEW_QUANTITY, :DIFF_QUANTITY

Examples of MERGE

  1. Update books when present, or add new record if absent

    MERGE INTO books b
  USING purchases p
  ON p.title = b.title and p.type = 'bk'
  WHEN MATCHED THEN
    UPDATE SET b.desc = b.desc || '; ' || p.desc
  WHEN NOT MATCHED THEN
    INSERT (title, desc, bought) values (p.title, p.desc, p.bought);

  2. Using a derived table

    MERGE INTO customers c
  USING (SELECT * from customers_delta WHERE id > 10) cd
  ON (c.id = cd.id)
  WHEN MATCHED THEN
    UPDATE SET name = cd.name
  WHEN NOT MATCHED THEN
    INSERT (id, name) values (cd.id, cd.name);

  3. Together with a recursive CTE

    MERGE INTO numbers
  USING (
    WITH RECURSIVE r(n) AS (
      SELECT 1 FROM rdb$database
      UNION ALL
      SELECT n+1 FROM r WHERE n < 200
    )
    SELECT n FROM r
  ) t
  ON numbers.num = t.n
  WHEN NOT MATCHED THEN
    INSERT(num) VALUES(t.n);

  4. Using DELETE clause

    MERGE INTO SALARY_HISTORY
USING (
  SELECT EMP_NO
  FROM EMPLOYEE
  WHERE DEPT_NO = 120) EMP
ON SALARY_HISTORY.EMP_NO = EMP.EMP_NO
WHEN MATCHED THEN DELETE

  5. The following example updates the PRODUCT_INVENTORY table daily based on orders processed in the SALES_ORDER_LINE table.If the stock level of the product would drop to zero or lower, then the row for that product is removed from the PRODUCT_INVENTORY table.

    MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM (SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
  JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
  GROUP BY 1
) AS SRC (ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE
See also

[fblangref50-dml-select], [fblangref50-dml-insert], [fblangref50-dml-update], [fblangref50-dml-update-or-insert], [fblangref50-dml-delete]

EXECUTE PROCEDURE

Executes a stored procedure

Syntax
EXECUTE PROCEDURE procname
   [{ <inparam-list | ( <inparam-list> ) }]
   [RETURNING_VALUES { <outvar-list> | ( <outvar-list ) }]

<inparam-list> ::=
  <inparam> [, <inparam> ...]

<outvar-list> ::=
  <outvar> [, <outvar> ...]

<outvar> ::= [:]varname
Table 1. Arguments for the EXECUTE PROCEDURE Statement Parameters
Argument Description

procname

Name of the stored procedure

inparam

An expression evaluating to the declared data type of an input parameter

varname

A PSQL variable to receive the return value

Executes an executable stored procedure, taking a list of one or more input parameters, if they are defined for the procedure, and returning a one-row set of output values, if they are defined for the procedure.

“Executable” Stored Procedure

The EXECUTE PROCEDURE statement is most commonly used to invoke “executable” stored procedures to perform some data-modifying task at the server side — those that do not contain any SUSPEND statements in their code.They can be designed to return a result set, consisting of only one row, which is usually passed, via a set of RETURNING_VALUES() variables, to another stored procedure that calls it.Client interfaces usually have an API wrapper that can retrieve the output values into a single-row buffer when calling EXECUTE PROCEDURE in DSQL.

Invoking “selectable” stored procedures is also possible with EXECUTE PROCEDURE, but it returns only the first row of an output set which is almost surely designed to be multi-row.Selectable stored procedures are designed to be invoked by a SELECT statement, producing output that behaves like a virtual table.

Note

  • In PSQL and DSQL, input parameters may be any expression that resolves to the expected type.

  • Although parentheses are not required after the name of the stored procedure to enclose the input parameters, their use is recommended for the sake of readability.

  • Where output parameters have been defined in a procedure, the RETURNING_VALUES clause can be used in PSQL to retrieve them into a list of previously declared variables that conforms in sequence, data type and number with the defined output parameters.

  • The list of RETURNING_VALUES may be optionally enclosed in parentheses and their use is recommended.

  • When DSQL applications call EXECUTE PROCEDURE using the Firebird API or some form of wrapper for it, a buffer is prepared to receive the output row and the RETURNING_VALUES clause is not used.

Examples of EXECUTE PROCEDURE

  1. In PSQL, with optional colons and without optional parentheses:

    EXECUTE PROCEDURE MakeFullName
  :FirstName, :MiddleName, :LastName
  RETURNING_VALUES :FullName;

  2. In Firebird’s command-line utility isql, with literal parameters and optional parentheses:

    EXECUTE PROCEDURE MakeFullName ('J', 'Edgar', 'Hoover');
    Note

    In DSQL (e.g. in isql), RETURNING_VALUES is not used.Any output values are captured by the application and displayed automatically.

  3. A PSQL example with expression parameters and optional parentheses:

    EXECUTE PROCEDURE MakeFullName
  ('Mr./Mrs. ' || FirstName, MiddleName, upper(LastName))
  RETURNING_VALUES (FullName);

EXECUTE BLOCK

Creates an “anonymous” block of PSQL code in DSQL for immediate execution

Available in

DSQL

Syntax
EXECUTE BLOCK [(<inparams>)]
  [RETURNS (<outparams>)]
  <psql-module-body>

<inparams> ::=  <param_decl> = ? [, <inparams> ]

<outparams> ::=  <param_decl> [, <outparams>]

<param_decl> ::=
  paramname <domain_or_non_array_type> [NOT NULL] [COLLATE collation]

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<psql-module-body> ::=
  !! See Syntax of a Module Body !!
Table 1. Arguments for the EXECUTE BLOCK Statement Parameters
Argument Description

param_decl

Name and description of an input or output parameter

paramname

The name of an input or output parameter of the procedural block, up to 63 characters long.The name must be unique among input and output parameters and local variables in the block

collation

Collation

Executes a block of PSQL code as if it were a stored procedure, optionally with input and output parameters and variable declarations.This allows the user to perform “on-the-fly” PSQL within a DSQL context.

Examples

  1. This example injects the numbers 0 through 127 and their corresponding ASCII characters into the table ASCIITABLE:

    EXECUTE BLOCK
AS
declare i INT = 0;
BEGIN
  WHILE (i < 128) DO
  BEGIN
    INSERT INTO AsciiTable VALUES (:i, ascii_char(:i));
    i = i + 1;
  END
END

  2. The next example calculates the geometric mean of two numbers and returns it to the user:

    EXECUTE BLOCK (x DOUBLE PRECISION = ?, y DOUBLE PRECISION = ?)
RETURNS (gmean DOUBLE PRECISION)
AS
BEGIN
  gmean = SQRT(x*y);
  SUSPEND;
END

    Because this block has input parameters, it has to be prepared first.Then the parameters can be set and the block executed.It depends on the client software how this must be done and even if it is possible at all — see the notes below.

  3. Our last example takes two integer values, smallest and largest.For all the numbers in the range smallest…​largest, the block outputs the number itself, its square, its cube and its fourth power.

    EXECUTE BLOCK (smallest INT = ?, largest INT = ?)
RETURNS (number INT, square BIGINT, cube BIGINT, fourth BIGINT)
AS
BEGIN
  number = smallest;
  WHILE (number <= largest) DO
  BEGIN
    square = number * number;
    cube   = number * square;
    fourth = number * cube;
    SUSPEND;
    number = number + 1;
  END
END

    Again, it depends on the client software if and how you can set the parameter values.

Input and output parameters

Executing a block without input parameters should be possible with every Firebird client that allows the user to enter their own DSQL statements.If there are input parameters, things get trickier: these parameters must get their values after the statement is prepared, but before it is executed.This requires special provisions, which not every client application offers.(Firebird’s own isql, for one, doesn’t.)

The server only accepts question marks (“?”) as placeholders for the input values, not “:a”, “:MyParam” etc., or literal values.Client software may support the “:xxx” form though, and will preprocess it before sending it to the server.

If the block has output parameters, you must use SUSPEND or nothing will be returned.

Output is always returned in the form of a result set, just as with a SELECT statement.You can’t use RETURNING_VALUES or execute the block INTO some variables, even if there is only one result row.

PSQL Links

For more information about writing PSQL, consult Chapter Procedural SQL (PSQL) Statements.

Statement Terminators

Some SQL statement editors — specifically the isql utility that comes with Firebird, and possibly some third-party editors — employ an internal convention that requires all statements to be terminated with a semicolon.This creates a conflict with PSQL syntax when coding in these environments.If you are unacquainted with this problem and its solution, please study the details in the PSQL chapter in the section entitled Switching the Terminator in isql.