SQLite - UPDATE Query

In SQLite the UPDATE Statement can be used to modify the subset of the values of record in the table in a database.

If the UPDATE statement does not have a WHERE clause, then all rows in the table are modified by the UPDATE.

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

SQLite INSERT INTO Syntax

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

UPDATE table_name
SET column_1 = value_1, column_2 = value_2...., column_N = value_N
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 UPDATE a ADDRESS of the STUDENT whose ID is 3.

We can use the following SQLite statement:

sqlite> UPDATE STUDENTS SET ADDRESS = 'Velbert' WHERE ID = 3;
                            

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
7           John        Mevric      17          Wuppertal
8           Jerry       Bann        20          Velbert
                            

Modify all rows

If you want to modify all ADDRESS and AGE column values in STUDENTS table, we do not need to use WHERE clause. This can be done as shown below:

sqlite> UPDATE STUDENTS SET ADDRESS = 'Velbert', SALARY = 20;
                            

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

ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Velbert
2           Tom         white       20          Velbert 
3           Patric      Rossman     20          Velbert
4           Noor        Khan        20          Velbert
5           Julia       Tesar       20          Velbert
6           Tim         Netten      20          Velbert
7           John        Mevric      20          Velbert
8           Jerry       Bann        20          Velbert