SQLite UPDATE
Introduction
The UPDATE command in SQLite is used to modify the existing values of one or more columns in a table's existing rows. This command can update multiple rows simultaneously, but all the rows must belong to the same table. This is useful for making bulk changes to data in a single operation.
Syntax:
UPDATE table_name SET column_name=new_value [, ...] WHERE expression
Where,
- table_name: The name of the table whose rows will be updated.
- column_name: The column that will be updated.
- new_value: The new value to assign to the column.
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
Basic Update 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.
Creating and Updating Another Table
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.
Updating Multiple 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
Conditional Updates
Use conditional logic to update values based on specific conditions:
UPDATE prod_mast
SET prod_rate = prod_rate * 1.1
WHERE prod_rate < 100;
This updates the prod_rate column by increasing it by 10% for all products with a rate less than 100.
Tips for Efficient Updates
- Backup Data: Always backup your data before performing bulk updates.
- Use Indexes: Indexes can speed up the WHERE clause in UPDATE statements.
- Limit Scope: Use precise WHERE clauses to limit the scope of updates and avoid updating unintended rows.
- Monitor Performance: Keep an eye on performance, especially with large tables.
Frequently Asked Questions (FAQ) - SQLite UPDATE
1. What is the purpose of the UPDATE command in SQLite?
The UPDATE command is used to modify existing values of one or more columns in a table's rows.
2. Can the UPDATE command modify multiple rows at once?
Yes, the UPDATE command can modify multiple rows simultaneously, but all rows must belong to the same table.
3. What is required to specify in an UPDATE command?
We need to specify the table name, the columns to be updated, the new values for these columns, and an optional WHERE clause to determine which rows to update.
4. What happens if we do not include a WHERE clause in an UPDATE command?
If no WHERE clause is included, all rows in the table will be updated.
5. Can we update multiple columns in a single UPDATE command?
Yes, we can update multiple columns by separating each column and its new value with a comma.
6. Is it possible to update a table using values from another table?
Yes, we can use subqueries to update columns in one table based on values from another table.
7. How can we perform conditional updates in SQLite?
Conditional updates can be done using the WHERE clause to specify the conditions under which rows should be updated.
8. What precautions should you take before performing bulk updates?
It is recommended to backup our data, use precise WHERE clauses to limit the scope of updates, and monitor performance, especially with large tables.
9. How can indexes help with the UPDATE command?
Indexes can speed up the execution of the WHERE clause in UPDATE statements, improving overall performance.