SQLite: DELETE Statement
This SQLite tutorial 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 );
Advertisements