*** DRAFT ***

SQL As Understood By SQLite

DELETE

qualified-table-name:

The DELETE command is used to remove records from a table. The command consists of the "DELETE FROM" keywords followed by the name of the table from which records are to be removed.

Without a WHERE clause, all rows of the table are removed. If a WHERE clause is supplied, then only those rows that match the expression are removed.

Restrictions on DELETE Statements Within CREATE TRIGGER

There are additional syntax restrictions on DELETE statements that occur within the body of a CREATE TRIGGER statement. The table-name must be unqualified. In other words, the database-name. prefix on the table name is not allowed within triggers. The table from which to delete must be in the same database as the table to which the trigger is attached.

The INDEXED BY and NOT INDEXED clauses are not allowed on DELETE statements within triggers.

The LIMIT clause (described below) is unsupported within triggers.

The Truncate Optimization

When the WHERE is 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, 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.

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.

Use Of LIMIT

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:

The optional LIMIT clause can be used to limit the number of rows deleted, and thereby limit the size of the transaction. 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 determined by the ORDER BY clause.

The presence of a LIMIT clause defeats the truncate optimization causing all rows being deleted to be visited.