Update a value in a TABLE

In this very simple example we will see how to update a row in a sql database Since we are working in the notebook, we will load the sql extension in order to manipulate the database. The database mydatabase.db is a SQLite database already created before the example.

#load the extension
%load_ext sql 
#connect to the database
%sql sqlite:///mydatabase.db 

'Connected: @mydatabase.db'

The content of the TABLE is the following

%sql SELECT * FROM tutyfrutty

 * sqlite:///mydatabase.db
Done.

We want to update the kcal value for "Orange". We will use the UPDATE keyword in order to do so. We can use the index value in order to access the row and set a new value.

%sql UPDATE "tutyfrutty" SET "kcal" = 48 WHERE "index" = 1;
%sql SELECT * FROM tutyfrutty WHERE "index" = 1;

 * sqlite:///mydatabase.db
1 rows affected.
 * sqlite:///mydatabase.db
Done.

The WHERE keyword can be used to specify mutilple rows. For instance, if we want to change the kcal value of all yellow fruits :

%sql UPDATE "tutyfrutty" SET "kcal" = 126 WHERE "color" = "yellow";
%sql SELECT * FROM tutyfrutty

 * sqlite:///mydatabase.db
2 rows affected.
 * sqlite:///mydatabase.db
Done.