w3resource

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.


Previous: Insert
Next: Delete



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/sqlite/sqlite-update.php