Small. Fast. Reliable.
Choose any three.

SQL As Understood By SQLite

DELETE

delete-stmt:

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.

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 individual. 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 disabled for all queries by recompiling SQLite with the SQLITE_OMIT_TRUNCATE_OPTIMIZATION compile-time switch.

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


This page last modified 2009/02/14 18:02:23 UTC