Delete

Delete removes rows from a table. An optional where predicate restricts which rows are removed; without it, every row in the table is deleted.

Syntax

Railroad

deletefromtableasaliaswhereexpr;

BNF

delete-stmt ::= "delete" "from" identifier [ "as" identifier ] [ where-clause ] ";"

Rules

  1. Without where, every row in the table is removed. (phase: execute)
  2. The table alias is optional; when omitted, the table name is the binding name in the predicate. (phase: execute)
  3. The where predicate follows the same boolean semantics as query where (null is not-true). (phase: execute)

Examples

Minimal

Deleting From

Deleting from an empty table succeeds and yields nothing.

SQL

create table T;

delete from T;

select * from T;

Result

[]

Compound

Empties Table

Delete with no where removes every row.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3});

delete from T;

select * from T;

Result

[]

Matching Rows

Delete with a predicate removes only matching rows.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3});

delete from T where T.x > 1;

select * from T;

Result

[
  { "x": 1 }
]

An Explicit

An explicit as alias binds the predicate's references.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3});

delete from T as r where r.x = 2;

select * from T;

Result

[
  { "x": 1 },
  { "x": 3 }
]

Table Unchanged

A predicate that matches nothing leaves the table unchanged.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3});

delete from T where T.x > 100;

select * from T;

Result

[
  { "x": 1 },
  { "x": 2 },
  { "x": 3 }
]

Reuses Table

A table is reusable after a delete-all.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2});

delete from T;

insert into T ({"x": 9});

select * from T;

Result

[
  { "x": 9 }
]

Error cases

Deleting From

Deleting from an undeclared table is a static error.

SQL

create table T;

delete from Ghost;

Expected error: static

See also