DELETE
Sourcedelete-stmt:
WITH
RECURSIVE
common-table-expression
,
DELETE
FROM
qualified-table-name
returning-clause
expr
WHERE
common-table-expression:
table-name
(
column-name
)
AS
NOT
MATERIALIZED
(
select-stmt
)
,
select-stmt:
WITH
RECURSIVE
common-table-expression
,
SELECT
DISTINCT
result-column
,
ALL
FROM
table-or-subquery
join-clause
,
WHERE
expr
GROUP
BY
expr
HAVING
expr
,
WINDOW
window-name
AS
window-defn
,
VALUES
(
expr
)
,
,
compound-operator
select-core
ORDER
BY
LIMIT
expr
ordering-term
,
OFFSET
expr
,
expr
compound-operator:
UNION
UNION
INTERSECT
EXCEPT
ALL
join-clause:
table-or-subquery
join-operator
table-or-subquery
join-constraint
join-constraint:
USING
(
column-name
)
,
ON
expr
join-operator:
NATURAL
LEFT
OUTER
JOIN
,
INNER
CROSS
ordering-term:
expr
COLLATE
collation-name
DESC
ASC
NULLS
FIRST
NULLS
LAST
result-column:
expr
AS
column-alias
*
table-name
.
*
table-or-subquery:
schema-name
.
table-name
AS
table-alias
INDEXED
BY
index-name
NOT
INDEXED
table-function-name
(
expr
)
,
AS
table-alias
(
select-stmt
)
(
table-or-subquery
)
,
join-clause
window-defn:
(
base-window-name
PARTITION
BY
expr
,
ORDER
BY
ordering-term
,
frame-spec
)
frame-spec:
GROUPS
BETWEEN
UNBOUNDED
PRECEDING
AND
UNBOUNDED
FOLLOWING
RANGE
ROWS
UNBOUNDED
PRECEDING
expr
PRECEDING
CURRENT
ROW
expr
PRECEDING
CURRENT
ROW
expr
FOLLOWING
expr
PRECEDING
CURRENT
ROW
expr
FOLLOWING
EXCLUDE
CURRENT
ROW
EXCLUDE
GROUP
EXCLUDE
TIES
EXCLUDE
NO
OTHERS
expr:
literal-value
bind-parameter
schema-name
.
table-name
.
column-name
unary-operator
expr
expr
binary-operator
expr
function-name
(
DISTINCT
expr
)
filter-clause
over-clause
,
*
(
expr
)
,
CAST
(
expr
AS
type-name
)
expr
COLLATE
collation-name
expr
NOT
LIKE
GLOB
REGEXP
MATCH
expr
expr
ESCAPE
expr
expr
ISNULL
NOTNULL
NOT
NULL
expr
IS
NOT
expr
expr
NOT
BETWEEN
expr
AND
expr
expr
NOT
IN
(
select-stmt
)
expr
,
schema-name
.
table-function
(
expr
)
table-name
,
NOT
EXISTS
(
select-stmt
)
CASE
expr
WHEN
expr
THEN
expr
ELSE
expr
END
raise-function
filter-clause:
literal-value:
CURRENT_TIMESTAMP
numeric-literal
string-literal
blob-literal
NULL
TRUE
FALSE
CURRENT_TIME
CURRENT_DATE
over-clause:
OVER
window-name
(
base-window-name
PARTITION
BY
expr
,
ORDER
BY
ordering-term
,
frame-spec
)
frame-spec:
GROUPS
BETWEEN
UNBOUNDED
PRECEDING
AND
UNBOUNDED
FOLLOWING
RANGE
ROWS
UNBOUNDED
PRECEDING
expr
PRECEDING
CURRENT
ROW
expr
PRECEDING
CURRENT
ROW
expr
FOLLOWING
expr
PRECEDING
CURRENT
ROW
expr
FOLLOWING
EXCLUDE
CURRENT
ROW
EXCLUDE
GROUP
EXCLUDE
TIES
EXCLUDE
NO
OTHERS
ordering-term:
expr
COLLATE
collation-name
DESC
ASC
NULLS
FIRST
NULLS
LAST
raise-function:
RAISE
(
ROLLBACK
,
error-message
)
IGNORE
ABORT
FAIL
select-stmt:
WITH
RECURSIVE
common-table-expression
,
SELECT
DISTINCT
result-column
,
ALL
FROM
table-or-subquery
join-clause
,
WHERE
expr
GROUP
BY
expr
HAVING
expr
,
WINDOW
window-name
AS
window-defn
,
VALUES
(
expr
)
,
,
compound-operator
select-core
ORDER
BY
LIMIT
expr
ordering-term
,
OFFSET
expr
,
expr
compound-operator:
UNION
UNION
INTERSECT
EXCEPT
ALL
join-clause:
table-or-subquery
join-operator
table-or-subquery
join-constraint
join-constraint:
USING
(
column-name
)
,
ON
expr
join-operator:
NATURAL
LEFT
OUTER
JOIN
,
INNER
CROSS
ordering-term:
expr
COLLATE
collation-name
DESC
ASC
NULLS
FIRST
NULLS
LAST
result-column:
expr
AS
column-alias
*
table-name
.
*
table-or-subquery:
schema-name
.
table-name
AS
table-alias
INDEXED
BY
index-name
NOT
INDEXED
table-function-name
(
expr
)
,
AS
table-alias
(
select-stmt
)
(
table-or-subquery
)
,
join-clause
window-defn:
(
base-window-name
PARTITION
BY
expr
,
ORDER
BY
ordering-term
,
frame-spec
)
frame-spec:
GROUPS
BETWEEN
UNBOUNDED
PRECEDING
AND
UNBOUNDED
FOLLOWING
RANGE
ROWS
UNBOUNDED
PRECEDING
expr
PRECEDING
CURRENT
ROW
expr
PRECEDING
CURRENT
ROW
expr
FOLLOWING
expr
PRECEDING
CURRENT
ROW
expr
FOLLOWING
EXCLUDE
CURRENT
ROW
EXCLUDE
GROUP
EXCLUDE
TIES
EXCLUDE
NO
OTHERS
type-name:
name
(
signed-number
,
signed-number
)
(
signed-number
)
signed-number:
qualified-table-name:
schema-name
.
table-name
AS
alias
INDEXED
BY
index-name
NOT
INDEXED
returning-clause:
RETURNING
expr
AS
column-alias
*
,
The DELETE command removes records from the table identified by the qualified-table-name.
If the WHERE clause is not present, all records in the table are deleted. If a WHERE clause is supplied, then only those rows for which the WHERE clause boolean expression is true are deleted. Rows for which the expression is false or NULL are retained.
2. Restrictions on DELETE Statements Within CREATE TRIGGERThe following restrictions apply to DELETE statements that occur within the body of a CREATE TRIGGER statement:
The table-name specified as part of a DELETE statement within a trigger body must be unqualified. In other words, the schema-name. prefix on the table name is not allowed within triggers. If the table to which the trigger is attached is not in the temp database, then DELETE statements within the trigger body must operate on tables within the same database as it. If the table to which the trigger is attached is in the TEMP database, then the unqualified name of the table being deleted is resolved in the same way as it is for a top-level statement (by searching first the TEMP database, then the main database, then any other databases in the order they were attached).
The INDEXED BY and NOT INDEXED clauses are not allowed on DELETE statements within triggers.
The LIMIT and ORDER BY clauses (described below) are unsupported for DELETE statements within triggers.
The RETURNING clause is not supported for triggers.
If SQLite is compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT
compile-time option, then the syntax of the DELETE statement is
extended by the addition of optional ORDER BY and LIMIT clauses:
delete-stmt-limited:
WITH
RECURSIVE
common-table-expression
,
DELETE
FROM
qualified-table-name
WHERE
expr
returning-clause
ORDER
BY
ordering-term
,
LIMIT
expr
OFFSET
expr
,
expr
If a DELETE statement has a LIMIT clause, the maximum number of rows that
will be deleted is found by evaluating the accompanying expression and casting
it to an integer value. If the result of the evaluating the LIMIT clause
cannot be losslessly converted to an integer value, it is an error. A negative LIMIT value is interpreted as "no limit". If the DELETE statement also has an OFFSET clause, then it is similarly evaluated and cast to an
integer value. Again, it is an error if the value cannot be losslessly
converted to an integer. If there is no OFFSET clause, or the calculated
integer value is negative, the effective OFFSET value is zero.
If the DELETE statement has an ORDER BY clause, then all rows that would be deleted in the absence of the LIMIT clause are sorted according to the ORDER BY. The first M rows, where M is the value found by
evaluating the OFFSET clause expression, are skipped, and the following N, where N is the value of the LIMIT expression, are deleted.
If there are less than N rows remaining after taking the OFFSET clause
into account, or if the LIMIT clause evaluated to a negative value, then all
remaining rows are deleted.
If the DELETE statement has no ORDER BY clause, then all rows that
would be deleted in the absence of the LIMIT clause are assembled in an
arbitrary order before applying the LIMIT and OFFSET clauses to determine the subset that are actually deleted.
The ORDER BY clause on a DELETE statement is used only to determine which
rows fall within the LIMIT. The order in which rows are deleted is arbitrary
and is not influenced by the ORDER BY clause.
This means that if there is a RETURNING clause, the rows returned by
the statement probably will not be in the order specified by the
ORDER BY clause.
When the WHERE clause and RETURNING clause are both omitted
from a DELETE statement and the table being deleted has no triggers,
SQLite uses an optimization to erase the entire table content
without having to visit each row of the table individually.
This "truncate" optimization makes the delete run much faster.
Prior to SQLite version 3.6.5 (2008-11-12), the truncate optimization
also meant that the sqlite3_changes() and
sqlite3_total_changes() interfaces
and the count_changes pragma
will not actually return the number of deleted rows. That problem has been fixed as of version 3.6.5 (2008-11-12).
The truncate optimization can be permanently disabled for all queries
by recompiling
SQLite with the SQLITE_OMIT_TRUNCATE_OPTIMIZATION compile-time switch.
The truncate optimization can also be disabled at runtime using
the sqlite3_set_authorizer() interface. If an authorizer callback
returns SQLITE_IGNORE for an SQLITE_DELETE action code, then
the DELETE operation will proceed but the truncate optimization will
be bypassed and rows will be deleted one by one.