Here we will learn sqlite delete statement with example and how to use sqlite delete statement to delete rows in the table with example.
In SQLite, the DELETE command is used to delete or remove one or more rows from a single table. The rows are completely deleted from the table.
Following is the syntax of the SQLite Delete statement to delete rows from a table.
DELETE FROM table_name [WHERE conditions];
If you observe the above SQLite Delete statement, we used to delete statement and defined some properties to delete records from the table.
table_name - Its name of the table in which we want to perform DELETE operation.
WHERE conditions - Optional. The conditions that must be met for the records to be selected.
The SQLite Delete command requires only a table name and a conditional expression to pick out rows and the WHERE condition is used to pick specific rows to delete instead of deleting all rows from a table.
Following is the example of using the SQLite Delete statement to delete rows from the table for that first create a table called products and insert some data by using the following queries.
CREATE TABLE products
(
Product_id INTEGER PRIMARY KEY,
Product_name VARCHARNOTNULL,
Qty INTEGER
);
INSERT INTO products
values (102,'AutoCAD',49),
(103,'Rubber-band',230),
(104,'Cosmetics', 1200),
(105,'Eye make up',102),
(120,'AutoCAD',60)
Now run the following query to check records of products table.
sqlite>SELECT * FROM products;
Product_id Product_name Qty
---------- ------------ ----------
102 AutoCAD 49
103 Rubber-band 230
104 Cosmetics 1200
105 Eye make up 102
120 AutoCAD 60
Following is the example of using the SQLite DELETE statement to delete rows from the table.
DELETE
FROM products
WHERE product_id=102;
If you observe above SQLite DELETE example, it will delete row whose product id equal to 102.
Now we will run the SQLite Select statement to check products table records.
sqlite>SELECT * FROM products;
Product_id Product_name Qty
---------- ------------ ----------
103 Rubber-band 230
104 Cosmetics 1200
105 Eye make up 102
120 AutoCAD 60
Following is the example of using the SQLite Delete statement with a LIKE operator.
DELETE
FROM products
where product_name LIKE '%-%';
The above SQLite delete statement will delete all the records products table whose product name contains “-“ character.
Now we will run the SQLite Select statement to check products table records.
sqlite>SELECT * FROM products;
Product_id Product_name Qty
---------- ------------ ----------
104 Cosmetics 1200
105 Eye make up 102
120 AutoCAD 60
In case if you want to delete all the rows from a table then we need to write SQLite Delete statement like as shown below.
sqlite>DELETE FROM products
If you observe the SQLite Delete query it will delete all the rows or records from the table because we didn't define any particular conditions on the delete statement.
This is how we can use SQLite delete statement to delete rows from a table based on our requirements.