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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook