Table of Contents for

Using SQLite

Syntax

Common Usage

DELETE FROM database_name.table_name;
DELETE FROM database_name.table_name WHERE id = 42;

Description

The DELETE command permanently removes rows from a table. Any row that satisfies the WHERE expression will be removed. A WHERE condition that causes no rows to be deleted is not considered an error. If no WHERE condition is provided, it is assumed to always be true, and every row in the table will be deleted.

If no WHERE clause is provided, there are some situations when SQLite can simply truncate the whole table. This is much faster than deleting every row individually, but it skips any per-row processing. Truncation will only happen if the table has no triggers and is not part of a foreign key relationship (assuming foreign key support is enabled). Truncation can also be disabled by having an authorizer return SQLITE_IGNORE for the delete operation (see sqlite3_set_authorizer()).

If the SQLite library has been compiled with the optional SQLITE_ENABLE_UPDATE_DELETE_LIMIT directive, an optional ORDER BY...LIMIT clause may be used to delete a specific number of rows. See the SQLite website for more details.

When a DELETE appears within a trigger body, additional limitations apply. See CREATE TRIGGER.

Deleting data from a table will not decrease the size of the database file unless auto-vacuum mode is enabled. To recover space previously taken up by deleted data, the VACUUM command must be run.

See Also

INSERT, UPDATE, VACUUM, auto_vacuum [PRAGMA, Ap F], CREATE TRIGGER