SQLite UPDATE

Introduction

The UPDATE command is used to change the existing values to one or more columns of existing rows in a table. This command can update more than one rows at a time, but all of the rows must be part of the same table.

Syntax:

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

Where,

table_name - Name of the table, the row or rows of the table which will be updated.

new_value - The value which is going to be changed or newly assigned.

Here is sample table prod_mast.

prod_id               prod_name   prod_rate   prod_qc
--------------------  ----------  ----------  ----------
1                     Pancakes    75          OK
2                     Gulha       55          Problems
3                     Pakora      48          OK
4                     Pizza       200         OK
5                     Fudge       100         OK
6                     Candy       95          Not OK
7                     Chocolate   150         OK

Example:

If you want to update the prod_qc column with 'OK' which was not updated, the following UPDATE statement can be used.

UPDATE prod_mast SET prod_qc='OK' WHERE prod_qc<>'OK';

Here is result after update the prod_qc column with 'OK' of prod_mast table.

sqlite> SELECT * FROM prod_mast;
prod_id               prod_name   prod_rate   prod_qc
--------------------  ----------  ----------  ----------
1                     Pancakes    75          OK
2                     Gulha       55          OK
3                     Pakora      48          OK
4                     Pizza       200         OK
5                     Fudge       100         OK
6                     Candy       95          OK
7                     Chocolate   150         OK 

The above result shows the records formatted by blue color have been updated.

We are creating another table and insert rows. Here is the sample

sqlite> INSERT OR REPLACE INTO orders(ord_no, item_id, item_name, ord_qty, cost
   ...> VALUES(1,5, '', 100, 0),
   ...> (2,2, '', 95, 0),
   ...> (3,1, '', 150,0),
   ...> (4,2, '', 250,0),
   ...> (5,2, '', 300,0)
   ...> (6,10, '', 100, 0),
   ...> (7,8, '', 95, 0)
   ...> ;
sqlite> SELECT * FROM orders;
ord_no                item_id     item_name   ord_qty     cost
--------------------  ----------  ----------  ----------  ----------
1                     5                       100         0
2                     2                       95          0
3                     1                       150         0
4                     2                       250         0
5                     2                       300         0
6                     10                      100         0
7                     8                       95          0

Update using multiple tables

UPDATE orders SET item_name=(SELECT prod_name FROM prod_mast WHERE prod_id = item_id);

Here is the result after an update the item_name column of orders table with the prod_name of prod_mast table which are matching the specified criteria.

sqlite> SELECT * FROM orders;
ord_no                item_id     item_name   ord_qty     cost
--------------------  ----------  ----------  ----------  ----------
1                     5           Fudge       100         0
2                     2           Gulha       95          0
3                     1           Pancakes    150         0
4                     2           Gulha       250         0
5                     2           Gulha       300         0
6                     10          NULL        100         0
7                     8           NULL        95          0

The result shows the only two values of item_name column for 6th and 7th rows not been updated, for not matching the specified criteria.

UPDATE multiple fields or columns using Values from another table

Suppose the item_name column have been updated by blank space. Here is the table.

ord_no      item_id     item_name   ord_qty    cost
----------  ----------  ----------  ----------  ----------
1           5                       100         0
2           2                       95          0
3           1                       150         0
4           2                       250         0
5           2                       300         0
6           10                      100         0
7           8                       95          0

If you want to update the item_name and cost column of orders table by the value of prod_name, and product of prod_mast.prod_rate and orders.ord_qty, the following statement can be used.

UPDATE orders SET item_name=(
SELECT prod_mast.prod_name 
FROM prod_mast 
WHERE orders.item_id=prod_mast.prod_id), 
cost=(
SELECT prod_mast.prod_rate*orders.ord_qty 
FROM prod_mast 
WHERE orders.item_id=prod_mast.prod_id);

Here is result below after updation of orders table.

sqlite> SELECT * FROM orders;
ord_no      item_id     item_name   ord_qty     cost
----------  ----------  ----------  ----------  ----------
1           5           Fudge       100         10000
2           2           Gulha       95          5225
3           1           Pancakes    150         11250
4           2           Gulha       250         13750
5           2           Gulha       300         16500
6           10                      100
7           8                       95

Previous: Insert
Next: Delete