DELETE
NAME
DELETE: delete rows
SYNOPSIS
<delete statement: searched> ::=
DELETE [ WITH <XML lexically scoped options> ] FROM <table name>
[ FOR PORTION OF <period name> FROM <point in time> TO <point in time> ]
[ WHERE <search condition> ]
DESCRIPTION
DELETE removes the rows of a table, or through an updatable view of the table it is drawn from, for which the search condition is true; every row, without WHERE. The search condition is evaluated for every row before any is deleted. A foreign key that references a row deleted acts as it says: refuses the delete, deletes or changes the rows that refer to it, or sets their columns null or to their defaults. A DELETE that deletes no row completes with no data.
The table's triggers run as CREATE TRIGGER says: its BEFORE triggers once the rows are found, before any is deleted, its AFTER triggers at the end of the statement. A view with an INSTEAD OF trigger of the statement's event runs it in place of the change, and nothing is written through the view: its row count is of the rows the trigger was given. An updatable view over such a view runs its trigger the same way, and so does the positioned form through a cursor over either. The rows a referential action deletes or changes run their own table's triggers too.
FOR PORTION OF a period of the table (see CREATE TABLE) deletes only the time from its first point to its second. It deletes the rows the WHERE condition is true of whose period overlaps that time, and keeps each one's parts before and after it as rows of their own, whose period ends where the portion begins or begins where it ends. A part kept takes its identity column's next value and its generated columns again, and is inserted as an INSERT inserts a row, its INSERT triggers running; the constraints judge it with the rest as the statement ends. Its row count is of the rows deleted.
WITH's XML options, XMLNAMESPACES and XMLBINARY, are in scope of the whole statement, its search condition and subqueries too: see XML Values.
The positioned form, WHERE CURRENT OF a cursor, runs only in a program that embeds turborel; see DECLARE CURSOR, and, of a cursor named by an extended name, ALLOCATE CURSOR.
RULES
- The table exists and is not read-only: a base table, or a view that can be updated.
- The search condition contains no set function outside a subquery.
- The rows left, and those referential actions change, satisfy the constraints.
- The transaction is not READ ONLY, but for a temporary table.
- FOR PORTION OF names an application-time period of the table, a base table, not SYSTEM_TIME, a reserved word. Its points in time are datetimes comparable with the period's columns, reference no column of the table, and invoke no function that is not deterministic or modifies SQL-data.
- A row deleted from a system-versioned table is kept as a historical row, ending at the transaction timestamp.
PRIVILEGES
DELETE on the table. SELECT on each other table a subquery reads.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 2A000 | -103 | The table does not exist or is read-only; the search condition has a set function outside a subquery; a rule of FOR PORTION OF is broken (rule 5); WITH's XML options break a rule of XML Values |
| 2A000 | -104 | Its user lacks DELETE on the table, or SELECT on a table read |
| 23000 | -201 | A row deleted is referenced by a foreign key whose action refuses it, or a referential action breaks a constraint |
| 2201H | -301 | A row of a system-time period begins after the transaction timestamp: another transaction changed it later |
| TB911 | -911 | A row was written by another transaction, not committed under LOCKS.WAIT OFF, or committed after this one's snapshot at REPEATABLE READ or SERIALIZABLE; or a wait for it would deadlock or passed LOCKS.TIMEOUT_MS: the statement is undone, and may be run again |
| 25000 | -917 | The transaction is READ ONLY |
| 02000 | 100 | Not an error: no row was deleted |
A statement that fails deletes no row.
EXAMPLES
Of the table PARTS of UPDATE, as it was before those updates:
DELETE FROM PARTS WHERE QTY = 0;
SELECT ID FROM PARTS ORDER BY ID;
DELETE FROM PARTS WHERE ID = 99;
OK
ID
--------------
1
(1 row)
no data
DELETE FROM PARTS WHERE SUM(QTY) > 0;
turborel: SQLCODE -103 (SQLSTATE 2A000): a set function needs an argument source (a <table expression>), which this <search condition> does not have [X3.135-1986 §5.8 SR1] (at character 24)
Of a table of prices through time, a week of January deleted:
CREATE TABLE OFFERS (ITEM INTEGER, PRICE INTEGER, S DATE NOT NULL, E DATE NOT NULL, PERIOD FOR VALID (S, E));
INSERT INTO OFFERS VALUES (1, 10, DATE '2026-01-01', DATE '2026-02-01');
DELETE FROM OFFERS FOR PORTION OF VALID FROM DATE '2026-01-10' TO DATE '2026-01-17';
SELECT * FROM OFFERS ORDER BY S;
OK
OK
OK
ITEM | PRICE | S | E
--------------+--------------+------------+------------
1 | 10 | 2026-01-01 | 2026-01-10
1 | 10 | 2026-01-17 | 2026-02-01
(2 rows)
STANDARD
ISO/IEC 9075:1992 §13.7, the <delete statement: searched>, and
X3.135-1986 §8.5. FOR PORTION OF is ISO/IEC 9075-2:2023 §14.9 SR7a and
§15.8 GR9, Feature T181. WITH's XML options are ISO/IEC 9075-14:2006
draft §14.3, Features X082 and X132. Referential actions are SQL-92's [§11.8]. TB911 is this
implementation's own.