< Previous Page | Home Page | Next Page >
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.
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.
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;
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 |
< Previous Page | Home Page | Next Page >