6.5. DELETE

Used forDeleting rows from a table or view

Available inDSQL, ESQL, PSQL

Syntax 

DELETE
  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.18. Arguments for the DELETE Statement Parameters
ArgumentDescription

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.

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

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

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

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

6.5.5. RETURNING

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

ⓘ
Note

  • 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

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;