SQLite Delete Statement

Here we will learn sqlite delete statement with example and how to use sqlite delete statement to delete rows in the table with example.

SQLite Delete Statement

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.

Syntax of SQLite Delete Statement

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.

Example of SQLite Delete Statement

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

SQLite Delete with LIKE Operator

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

SQLite Delete All Rows from Table

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.