SQLite Update Statement

Here we will learn sqlite update statement with example and how to use sqlite update statement to update one or multiple column values in the table with examples.

SQLite Update Statement

In SQLite, the UPDATE command is useful to assign new values to one or more columns of existing rows in a table. The SQLite UPDATE command can update more than one row, but all of the rows must be part of the same table.

 

In case if we want to update a single row in the table means we need to define where clause with update statement based on our requirement otherwise update statement will update all the rows in a table.

Syntax of SQLite Update Statement

Following is the syntax of the SQLite UPDATE statement to update single or multiple columns in a table.

 

UPDATE table_name SET column_name=new_value [, ...] WHERE expression

If you observe the above SQLite update statement, we are updating columns in a table by using the SET property. Here in SQLite update statement, we defined few properties those are

 

table_name - Indicates the name of a table in which you want to perform the update operation.

 

column_name - The columns that you wish to update.

 

new_value - The new values to assign to column. It can be an expression or a simple value.

 

Expression - conditions or expressions that must be met for the records to update.

 

The SQLite UPDATE statement requires a table name followed by a list of column names or value pairs that should be assigned and the rows updation determined by a conditional expression that is tested against each row of the table.

Example of SQLite Update Statement

We will see how to use the SQLite UPDATE statement to update table rows with an example for that first create a table called products and insert some data by using the following queries.

 

CREATE TABLE products

(Product_id INTEGER PRIMARY KEY AUTOINCREMENT,

Product_name VARCHAR NOT NULL,

Qty INTEGER);

 

INSERT INTO products

values (102,'AutoCAD',58),

(103,'Rubber-band', 230),

(104,'Cosmetics', 1200),

(105,'Eye make up', 102),

(120,'AutoCAD',50);

Now run the following query to check records of products table.

 

sqlite> SELECT * FROM products;

 

Product_id  Product_name  Qty

----------  ------------  ----------

102         AutoCAD       58

103         Rubber-band   230

104         Cosmetics     1200

105         Eye make up   102

120         AutoCAD       50

SQLite UPDATE Single Row of Column

Now we will write the SQLite UPDATE query to update a single record in products table like as shown below.

 

sqlite> UPDATE products SET Qty=41 WHERE product_id=102;

In the above SQLite UPDATE query, we are updating the Qty column value of the products table where productid = 102. After update check products tables records whether those are updated or not by using the following query.

 

sqlite> SELECT * FROM products;

 

Product_id  Product_name  Qty

----------  ------------  ----------

102         AutoCAD       41

103         Rubber-band   230

104         Cosmetics     1200

105         Eye make up   102

120         AutoCAD       50

If you observe the above result it updated only 1 record whose product_id is equal to 102.

SQLite Update Multiple Rows of Single Column

Following is the example of SQLite UPDATE query to update multiple rows of a single column.

 

UPDATE products SET

Qty=Qty + (Qty*20)/100

WHERE product_name LIKE 'a%';

In the above example, it will update all products quantity by 20% more whose product name starts with ‘a’ or ‘A’ character. Once we execute the above query our products table records will be like as shown below.

 

sqlite> SELECT * FROM products;

 

Product_id  Product_name  Qty

----------  ------------  ----------

102         AutoCAD       49

103         Rubber-band   230

104         Cosmetics     1200

105         Eye make up   102

120         AutoCAD       60

SQLite Update Multiple Columns

Following is the example of the SQLite UPDATE query to update multiple columns in a table.

 

UPDATE products SET

Qty=100,product_name='CAD'

WHERE product_id = 102

In the above SQLite update example, we are updating Qty, product_name column values in products table where product_id = 102. It will update the row value whose product id equal to 102. Now run and check products table records.

 

sqlite> SELECT * FROM products;

 

Product_id  Product_name  Qty

----------  ------------  ----------

102         CAD           100

103         Rubber-band   230

104         Cosmetics     1200

105         Eye make up   102

120         AutoCAD       60

This is how we can use the SQLite UPDATE statement to update records in a table based on our requirements.

Topics Covered