SQLite - DELETE Query

In SQLite the DELETE Statement can be used to remove the records from the table in a database.

If the DELETE statement does not have a WHERE clause, then all rows in the table are deleted by the DELETE command.

If the DELETE statement have a WHERE clause, then only those rows in the table are deleted which fulfills the where clause condition.

SQLite INSERT INTO Syntax

In SQLite it is possible to use DELETE query using the following syntax.

DELETE FROM table_name
WHERE [condition];

In the above syntax the condition can be one or more.

Example:

So let's consider we have created SCHOOL database. And we have created a STUDENTS table in this database.

The STUDENTS table have five columns (ID, NAME, SURNAME, AGE, ADDRESS).

The STUDENTS table also have some data inside it as shown below:

ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich
2           Tom         white       21          Cologne 
3           Patric      Rossman     19          Essen
4           Noor        Khan        22          Bonn
5           Julia       Tesar       18          Berlin
6           Tim         Netten      20          Frankfurt
7           John        Mevric      17          Wuppertal
8           Jerry       Bann        20          Velbert

So Let's say we want to DELETE the STUDENT whose ID is 7.

We can use the following SQLite statement:

sqlite> DELETE FROM STUDENTS WHERE ID = 7;

Now when we select the STUDENTS table again, it will look as below:

ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich
2           Tom         white       21          Cologne 
3           Patric      Rossman     19          Velbert
4           Noor        Khan        22          Bonn
5           Julia       Tesar       18          Berlin
6           Tim         Netten      20          Frankfurt
8           Jerry       Bann        23          Bonn

delete all rows

If you want to delete all rows in STUDENTS table, we do not need to use WHERE clause. This can be done as shown below:

sqlite> DELETE FROM STUDENTS;

Now when we select the STUDENTS table again, it will look as below:

ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------

SqLite Tutorial

SqLite - HOME SqLite - Intro SqLite - Installation SqLite - Syntax SqLite - Data Types SqLite - Create DB SqLite - Create Table SqLite - Drop Table SqLite - Select SqLite - Distinct SqLite - Where SqLite - And & Or SqLite - Order By SqLite - Insert Into SqLite - Update SqLite - Delete SqLite - Injection SqLite - Select Top SqLite - Like SqLite - Wildcards SqLite - In SqLite - Between SqLite - Aliases SqLite - Joins SqLite - Inner Join SqLite - Left Join SqLite - Right Join SqLite - Full Join SqLite - Union SqLite - Select Into SqLite - Insert Into Select SqLite - Constraints SqLite - Not Null SqLite - Unique SqLite - Primary Key SqLite - Foreign Key SqLite - Check SqLite - Default SqLite - Create Index SqLite - Drop SqLite - Alter SqLite - Auto Increment SqLite - Views SqLite - Dates SqLite - Null Values SqLite - Null Functions SqLite - Data Types SqLite - DB Data Types

SqLite Functions

SqLite - Functions SqLite - Avg() SqLite - Count() SqLite - First() SqLite - Last() SqLite - Max() SqLite - Min() SqLite - Sum() SqLite - Group By SqLite - Having SqLite - Ucase() SqLite - Lcase() SqLite - Mid() SqLite - Len() SqLite - Round() SqLite - Now() SqLite - Format()