SQLite: DELETE Statement

This SQLite post explains how to use the SQLite DELETE statement with syntax and examples.

Description

The SQLite DELETE statement is used to delete a single record or multiple records from a table in SQLite.

Syntax

The syntax for the DELETE statement in SQLite is:

DELETE FROM table
[WHERE conditions];

Parameters or Arguments

table

The table that you wish to delete records from.

WHERE conditions

Optional. The conditions that must be met for the records to be deleted. If no conditions are provided, then all of the records in the table will be deleted.

Note

• You do not need to list fields in the SQLite DELETE statement since you are deleting the entire row from the table.
• SQLite does not appear to support the DELETE LIMIT statement.

Example - Delete with 1 Condition

Let's look at a simple SQLite DELETE query example, where we just have one condition in the DELETE statement.

For example:

DELETE FROM employees
WHERE last_name = 'Smith';

This SQLite DELETE example would delete all records from the employees table where the last_name is 'Smith'.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SQLite SELECT statement before performing the delete.

SELECT count(*)
FROM employees
WHERE last_name = 'Smith';

Example - Delete with 2 Conditions

Let's look at a SQLite DELETE example, where we just have two conditions in the DELETE statement.

For example:

DELETE FROM employees
WHERE last_name = 'Smith'
AND employee_id  50;

This SQLite DELETE example would delete all records from the employees table where the last_name is 'Smith' and the employee_id is less than 50.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SQLite SELECT statement before performing the delete.

SELECT count(*)
FROM employees
WHERE last_name = 'Smith'
AND employee_id  50;

Example - Delete using EXISTS Condition

You can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the SQLite FROM clause when you are performing a delete, you can use the SQLite EXISTS clause.

For example:

DELETE FROM employees
WHERE EXISTS
  ( SELECT *
    FROM positions
    WHERE positions.position_id = employees.position_id );

This SQLite DELETE example would delete all records in the employees table where there is a record in the positions table based on the position_id field.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted byrunning the following SQLite SELECT statement before performing the delete.

SELECT COUNT(*) FROM employees
WHERE EXISTS
  ( SELECT *
    FROM positions
    WHERE positions.position_id = employees.position_id );