Open In Colab

Manipulating Databases: INSERT INTO and UPDATE

So far we have only queried existing information. Sometimes it might be necessary, however, to change the information within a table. This can be done via the INSERT and UPDATE Statements.

  • The INSERT Statement is used to insert new data into an existing table
  • The UPDATE Statement is used to change existing data in an existing table

INSERT INTO Statement

To insert data into a table, you use the INSERT statement. SQLite provides various forms of the INSERT statements that allow you to insert a single row, multiple rows, and default values into a table. The last option will be ignored in this course.

Inserting a single row into a table

To insert a single row into a table, you use the following form of the INSERT statement:

INSERT INTO table (column1,column2 ,..)
VALUES( value1, value2 ,...);

First, specify the name of the table to which you want to insert data after the INSERT INTO keywords. Second, add a comma-separated list of columns after the table name. The column list is optional. However, it is a good practice to include the column list after the table name. Third, add a comma-separated list of values after the VALUES keyword. If you omit the column list, you have to specify values for all columns in the value list. The number of values in the value list must be the same as the number of columns in the column list.


We will use the STUDENT table from before as an example.

ROLL_NO NAME AGE BRANCH CITY
10001 Kate 22 CSE Mumbai
10002 Richard 21 ECE Delhi
10003 Rick 33 ME Chennai
10004 Peter 26 CSE Delhi
10005 Peter 22 CSE Noida
10006 Mark 22 ECE Jaipur
10007 Brad 23 ME Rampur

Let's insert a new student in our table with the following statement:

INSERT INTO STUDENT (NAME, AGE, BRANCH, CITY)
VALUES ('Bob', 21, 'CSE', 'Mumbai');

This will result in the following table:

ROLL_NO NAME AGE BRANCH CITY
10001 Kate 22 CSE Mumbai
10002 Richard 21 ECE Delhi
10003 Rick 33 ME Chennai
10004 Peter 26 CSE Delhi
10005 Peter 22 CSE Noida
10006 Mark 22 ECE Jaipur
10007 Brad 23 ME Rampur
10008 Bob 21 CSE Mumbai

Because the ROLL_NO column is an auto-increment column (it's the primary key), you can ignore it in the INSERT INTO statement. SQLite automatically geneate a sequential integer number to insert into this column.


Inserting multiple rows into a table

To insert multiple rows into a table, you use the following form of the INSERT statement:

INSERT INTO table1 (column1,column2 ,..)
VALUES 
   (value1,value2 ,...),
   (value1,value2 ,...),
    ...
   (value1,value2 ,...);

Each value list following the VALUES clause is a row that will be inserted into the table.


UPDATE Statement

To update existing data in a table, you use the SQLite UPDATE statement. The following illustrates the syntax of the UPDATE statement:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

First, specify the table you want to update after the UPDATE clause. Second, set new value for each column of the table in the SET clause. Third, specify rows to update using a condition in the WHERE clause. The WHERE clause is optional. If you skip it, the UPDATE statement will update data in all rows of the table.

*Updating one column

We will use the STUDENT table from above to demonstrate the UPDATE statement.

Suppose, Brad changes departments from ME to CSE In this case, you can update Brad's department using the following statement:

UPDATE STUDENT
SET BRANCH = 'CSE'
WHERE ROLL_NO = 10007;

As expected, this will result in:


ROLL_NO NAME AGE BRANCH CITY
10001 Kate 22 CSE Mumbai
10002 Richard 21 ECE Delhi
10003 Rick 33 ME Chennai
10004 Peter 26 CSE Delhi
10005 Peter 22 CSE Noida
10006 Mark 22 ECE Jaipur
10007 Brad 23 CSE Rampur


Update multiple columns

Suppose Rick moves to Delhi AND changes his department to ECE.You can use the UPDATE statement to update multiple columns as follows:

UPDATE STUDENT
SET CITY = 'Delhi',
    BRANCH = 'ECE',
WHERE
    ROLL_NO = 10003;

DELETE Statement

You have learned how to insert a new row into a table and update existing data of a table. Sometimes, you need to remove rows from a table. In this case, you use the SQLite DELETE statement.

The SQLite DELETE statement allows you to delete one row, multiple rows, and all rows in a table. The syntax of the SQLite DELETE statement is as follows:

DELETE FROM table
WHERE Condition;

First, specify the name of the table which you want to remove rows after the DELETE FROM keywords. Second, add a search condition in the WHERE clause to identify the rows to remove. The WHERE clause is an optional part of the DELETE statement. If you omit the WHERE clause, the DELETE statement will delete all rows in the table.

Let's look one more time at our STUDENT example:

ROLL_NO NAME AGE BRANCH CITY
10001 Kate 22 CSE Mumbai
10002 Richard 21 ECE Delhi
10003 Rick 33 ME Chennai
10004 Peter 26 CSE Delhi
10005 Peter 22 CSE Noida
10006 Mark 22 ECE Jaipur
10007 Brad 23 ME Rampur

Let's imagine that the school runs out of money and has to close its CSE Branch. This means that all students from that branch need to be deleted from our database. This can be done as follows:

DELETE FROM STUDENT
WHERE BRANCH = 'CSE';

which results in the following table:

ROLL_NO NAME AGE BRANCH CITY
10002 Richard 21 ECE Delhi
10003 Rick 33 ME Chennai
10006 Mark 22 ECE Jaipur
10007 Brad 23 ME Rampur
In [ ]: