SQLite - DELETE Query

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
----------  ----------  ----------  ----------  ----------