Used forDeleting rows from a table or view

Available inDSQL, ESQL, PSQL


   |  FROM target [[AS] alias]
   |  [WHERE {<search-conditions> | CURRENT OF cursorname}]
   |  [PLAN <plan_items>]
   |  [ORDER BY <sort_items>]
   |  [ROWS m [TO n]]
   |  [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 6.18Arguments for the DELETE Statement Parameters


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


Alias for the target table or view


Search condition limiting the set of rows being targeted for deletion


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


Query plan clause


ORDER BY clause

m, n

Integer expressions for limiting the number of rows being deleted


An expression to be returned in the RETURNING clause


An expression whose value is used for returning


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.


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.


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';


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.


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.

   |DELETE FROM People
   |  WHERE firstname <> 'Boris' AND lastname <> 'Johnson';
   |DELETE FROM employee e
   |    SELECT *
   |    FROM employee_project ep
   |     WHERE e.emp_no = ep.emp_no);
   |DELETE FROM Cities
   |  WHERE CURRENT OF Cur_Cities;  -- ESQL and PSQL only


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


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

6.5.4ORDER 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


Deleting the oldest purchase:

  |DELETE FROM Purchases
  |  ORDER BY date ROWS 1;

Deleting the highest custno(s):

  |  ORDER BY custno DESC ROWS 1 to 10;

Deleting all sales, ORDER BY clause pointless:

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


A DELETE statement removing at most one row may optionally include a RETURNING clause in order to return values from the deleted row. 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.*.

  • In DSQL, a statement with RETURNING always returns a singleton, never a multi-row set. If a RETURNING clause is present and more than one matching record is found, an error multiple rows in singleton select is raised. If no records are deleted, the returned columns contain NULL. This behaviour may change in future Firebird versions

  • The INTO clause is available only in PSQL

    • If the row is not deleted, nothing is returned and the target variables keep their values

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