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