SQLite Update Statement

  Total Views : 39
  Comments : 0
 
  Keywords : SQLite update query with example, SQLite update column value for all rows with example, SQLite update multiple columns in one statement with example, SQLite update table column value example

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

SQLite Update Statement

In SQLite UPDATE command is used 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 single row in table means we need to define where clause with update statement based on our requirement otherwise update statement will update all the rows in table.

Syntax of SQLite Update Statement

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

 

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

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

 

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

 

column_name - The columns that you wish to update.

 

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

 

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

 

The SQLite UPDATE statement requires a table name followed by a list of column name 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 SQLite UPDATE statement to update table rows with example for that first create a table called products and insert some data by using 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 SQLite UPDATE query to update single record in products table like as shown below.

 

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

In above SQLite UPDATE query, we are updating Qty column value of products table where productid = 102. After update check products tables records whether those are updated or not by using 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 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 single column.

 

UPDATE products SET

Qty=Qty + (Qty*20)/100

WHERE product_name LIKE 'a%';

In above example, it will update all products quantity by 20% more whose product name starts with ‘a’ or ‘A’ character. Once we execute 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 SQLite UPDATE query to update multiple columns in table.

 

UPDATE products SET

Qty=100,product_name='CAD'

WHERE product_id = 102

In above SQLite update example, we are updating Qty, product_name column values in products table where product_id = 102. It will update 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 SQLite UPDATE statement to update records in table based on our requirements.

 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User