w3resource logo


sqlite images

SQLite DELETE

Secondary Nav

Introduction

The DELETE command is used to delete or remove one or more rows from a single table. When deleting all rows from the table this will not delete the structure of the table, it will remain same only the records are deleted. To delete a table from the database the DROP command is used.

Points to remember:

  • It is good practice to use DELETE command with a "WHERE" clause unless you intend to discard all the records in a table
  • It is very important to specify the right criteria to be certain that you are in going to delete the correct records.
Syntax:
DELETE FROM [database-name .] table-name [WHERE expr]

Here is the sample table prod_backup

prod_id               prod_name   prod_rate   prod_qc
--------------------  ----------  ----------  ----------
1                     Pancakes    75          OK
2                     Gulha       55          Problems
3                     Pakora      48          OK
4                     Pizza       200         OK
5                     Fudge       100         OK
6                     Candy       95          Not OK
7                     Chocolate   150         OK

Example

If you want to delete the rows from prod_backup table which pord_id is 4, the following statement can be used.

DELETE FROM prod_backup WHERE prod_id=4;

Here is the table after deletion.

sqlite> SELECT * FROM prod_backup;
prod_id               prod_name   prod_rate   prod_qc
--------------------  ----------  ----------  ----------
1                     Pancakes    75          OK
2                     Gulha       55          Problems
3                     Pakora      48          OK
5                     Fudge       100         OK
6                     Candy       95          Not OK
7                     Chocolate   150         OK

Look from the above result the pro_id contain the value 4 has been deleted.

Delete all rows from a table

If you want to delete all rows from the table prod_backup, the following statement can be used.

DELETE FROM prod_backup;

Here is the table after deletion.

sqlite> SELECT * FROM prod_backup;
prod_id               prod_name   prod_rate   prod_qc
--------------------  ----------  ----------  ----------
1                     Pancakes    75          OK
2                     Gulha       55          Problems
3                     Pakora      48          OK
5                     Fudge       100         OK
6                     Candy       95          Not OK
7                     Chocolate   150         OK

Look from the above result the pro_id contain the value 4 has been deleted.



Join our Question Answer community to learn and share your programming knowledge.