SQLite DELETE

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.

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.

Previous: Update
Next: Union