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

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