SQLite | Adding, Updating, and Deleting Data | Deleting Data (DELETE)
This article explains how to delete data stored in a table with the DELETE statement.
Deleting Data from a Table
Use the DELETE statement to delete data from a table. Its syntax is as follows.
DELETE FROM table_name WHERE condition;
Specify the rows to delete with a condition in the WHERE clause. If multiple rows match the condition, they are deleted together. Omitting the WHERE clause deletes all rows in the table.
Let’s try an example. Create the following table and add data to it.
create table user (id integer, name text, old integer);
insert into user values (1, 'devkuma', 32);
insert into user values (2, 'kimkc', 15);
insert into user values (3, 'araikuma', 18);
insert into user values (4, 'happykuma', 24);
insert into user values (5, 'mykuma', 19);
sqlite> create table user (id integer, name text, old integer);
sqlite>
sqlite> insert into user values (1, 'devkuma', 32);
sqlite> insert into user values (2, 'kimkc', 15);
sqlite> insert into user values (3, 'araikuma', 18);
sqlite> insert into user values (4, 'happykuma', 24);
sqlite> insert into user values (5, 'mykuma', 19);
sqlite>
First, delete rows whose old value is less than 20, and then query the table to verify that they were deleted.
delete from user where old < 20;
select * from user;
sqlite> delete from user where old < 20;
sqlite>
sqlite> select * from user;
id name old
---------- ---------- ----------
1 devkuma 32
4 happykuma 24
sqlite>
Next, delete all data from the table. The following statement removes every row.
delete from user;
select * from user;
sqlite> delete from user;
sqlite>
sqlite> select * from user;
sqlite>