SQLite INSERT, UPDATE, DELETE Query with Example

The data modification clauses in SQLite are INSERT, UPDATE, and DELETE statements. It is used for inserting new rows, updating existing values, or deleting rows from the database.

Note that, for all the following examples, you have to run the sqlite3.exe and open a connection to the sample database as flowing:

Step 1) In this step,

  1. Open My Computer and navigate to the following directory “C:\sqlite” and
  2. Then open “sqlite3.exe“:

SQLite Query - INSERT, UPDATE, DELETE

Step 2) Open the database “TutorialsSampleDB.db” by the following command:

.open TutorialsSampleDB.db

SQLite Query - INSERT, UPDATE, DELETE

Now you are ready to run any type of query on the database.

SQLite INSERT

SQLite INSERT is used to insert records into a specified table of the database. you have to use the ‘INSERT’ clause. The INSERT clause syntax is as follows:

SQLite INSERT

  • After the INSERT clause, you should state which table you need to insert the values into.
  • After the table name you write the list of columns, you want to insert the values into.
  • You can ignore the columns name and don’t write to them.
  • If you don’t write the columns name, the values will be inserted into all the columns found in the table with the same order, the columns are defined in the table.
  • After the VALUES clause, you should list the values to be inserted.
  • Each INSERT clause inserts only one row. If you want to insert multiple rows, you should write multiple INSERT clauses, one for each row.

SQLite Insert Example

In the following example, we will insert 2 rows into the students table, one for each student:

INSERT INTO Students(StudentId, StudentName, DepartmentId, DateOfBirth)
              VALUES(11, 'Ahmad', 4, '1997-10-12');

INSERT INTO Students VALUES(12, 'Aly', 4, '1996-10-12');

This should run successfully and there is no output for this:

SQLite Insert

This will insert two students:

  • The first student with StudentId=11, StudentName = Ahmad, DepartmentId = 4, and DateOfBirth = 1997-10-12.
  • The second student with StudentId=12, StudentName = Aly, DepartmentId = 4, and DateOfBirth = 1996-10-12′.

In the first statement, we listed the columns names “StudentId, StudentName, DepartmentId, DateOfBirth“.However, in the second statement, we didn’t.

The four values “12, ‘Aly’, 4, ‘1996-10-12’” will be inserted in all the four columns of the Students table in the same order the columns are defined.

Now, let’s verify that the two students were inserted into the Students table by running the following query:

SELECT * FROM Students;

Then you should see the two students returned from that query as following:

SQLite Insert

SQLite Update

SQLite UPDATE Query is used to modifying the existing records in a table. You can use WHERE clause with UPDATE query to update selected rows. The UPDATE clause updates a table by changing a value for a specific column. The following is the syntax of the UPDATE clause:

SQLite Update

As following:

  • After the “update clause”, you should write the table name to update.
  • You have to write “SET clause” which is used to write the column name to update and the value to be updated.
  • You can update more than one column. You can use a comma between each line.
  • You can specify a WHERE clause to specify some rows only. Only the rows that the expression evaluates to true are updated. If you didn’t specify a WHERE clause, all the rows will be updated.

SQLite Update Example

In the following UPDATE statement, we will update the DepartmentId for the Student with StudentId = 6 to be 3:

UPDATE Students
SET DepartmentId = 3 
WHERE StudentId = 6;

This should run successfully and you shouldn’t get any output:

SQLite Update

In the UPDATE clause, we specified that we want to update the table Students.

  • In the WHERE clause, we filtered all the students to select only the row for StudentId = 6.
  • The SET clause will update the value of the Department Id for the selected students to be 3.

Now, let’s verify that the student with ID 6 is updated, by running the following command:

SELECT * FROM Students WHERE StudentId = 6;

You should now see that the Department Id value is now 3 as following:

SQLite Update

SQLite Delete

SQLite DELETE query is used to remove existing records from a specified table. You can use the WHERE clause with DELETE queries to delete the selected rows.

DELETE clause has the following syntax:

SQLite Delete

  • You have to write a table name after the DELETE FROM clause, from which you want to delete records. (Note: that the DELETE clause is used to delete some records from a table or delete all the records and it won’t delete the table itself. However, the DROP clause is used to delete the entire table with all the records on it.)
  • If you write the DELETE clause like this “DELETE FROM guru”, this will delete all the records from the table “guru”.
  • You can specify a WHERE condition with an expression if you want to delete some specific rows. Only the rows for which the expression evaluates to true will be deleted. For example, “DELETE FROM guru WHERE id > 5” – this will delete only the records that have id larger than 5.

Example

In the following statement, we will delete two students with StudentId 11 and 12:

DELETE FROM Students WHERE StudentId = 11 OR StudentId = 12;

The expression “StudentId = 11 OR StudentId = 12” will be true for only students with Ids 11 and 12. So the DELETE clause will be applied on both and will delete them only.

This command should run successfully and you shouldn’t get any output as following:

SQLite Delete

You can verify that the two students were deleted, by selecting all the records from the Students table as following:

SELECT * FROM Students;

You shouldn’t see the two students with ids 11 and 12 as follows:

SQLite Delete

SQLite Conflict Clause

Suppose you have a column that has one of the following column constraints: UNIQUE, NOT NULL, CHECK, or PRIMARY KEY. And then you tried to insert or update a value on that column with a value that conflicts with this constraint.

For example, if a column has a UNIQUE constraint and you tried to insert a value that already exist (a duplicate value), which conflicts with the UNIQUE constraint. Then the CONFLICT clause let you choose what to do in such cases to resolve this conflict.

Before we continue explaining how CONFLICT clause resolve the conflict. You should understand what is a database transaction.

Database Transaction

The term database transaction is a list of SQLite operations (insert or update or delete). The database transaction must be executed as one unit, either all of the operations executed successfully or not at all. All the operations will be cancelled if one of them failed to execute.

Example for a database transaction

The transaction for transferring money from one bank account to another will involve a couple of activities. This transaction operation includes the withdrawal of money from the first account, and depositing it into another account. This transaction must fully completed or fully cancelled and not to fail halfway.

Here is the list of five resolutions you can choose in the CONFLICT clause:

  1. ROLLBACK – this will roll back the transaction in which the current SQLite statement that has the conflict (it will cancel the whole transaction). For example, if you are trying to update 10 rows, and the fifth row has a value that conflict with a constraint, then no rows will be updated, the 10 rows will stay the same. An error will be thrown.
  2. ABORT – this will abort (cancel) the current SQLite statement only that has the conflict and the transaction won’t be cancelled. For example, if you are trying to update 10 rows, and the fifth row has a value that conflict with a constraint, then only the fifth value won’t be updated but the other 9 rows will be updated. An error will be thrown.
  3. FAIL – aborts the current SQLite statement that has the conflict. However, the transaction won’t continue but the previous changes made to rows prior to the row that has the conflict will be committed. For example, if you are trying to update 10 rows, and the fifth row has a value that conflict with a constraint, then only the 4 rows will be updated and the other won’t. An error will be thrown.
  1. IGNORE – this will skip the row that contains the constraint violation and continue processing the other following rows of the SQLite statement. For example, if you are trying to update 10 rows, and the fifth row has a value that conflict with a constraint, then only the 4 rows will be updated and the other won’t. It won’t proceed further to update other rows and stop at the row that has the conflict value. No error will be thrown.
  1. REPLACE – it depends on the type of constraint that has the violation:
  • When there is a constraint violation for the UNIQUE or PRIMARY KEY constraint. The REPLACE will replace the row that causes the violation with the new inserted or updated row.
  • When there is a NOT NULL constraint violation, the REPLACE clause will replace the NULL value with the default value of that column. If the column doesn’t have a default value, then SQLite will abort the statement (statement will be cancelled)
  • IF the CHECK constraint violation occurs, the clause will be aborted.

Note: The above 5 resolutions are options for how do you want to resolve the conflict. It may not be necessarily what is applicable to resolve one conflict is applicable to resolve other types of conflicts.

How to declare the CONFLICT clause

You can declare the ON CONFLICT clause when you define a constraint for a column definition within the CREATE TABLE clause. Using the following syntax:

SQLite Conflict Clause

You can choose one from the five resolutions to resolve the conflict as explained before.

ON CONFLICT IGNORE Example

Step 1) Create a new table subject as follows:

CREATE TABLE [Subjects] (  
    [SubjectId] INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE,
    [SubjectName] NVARCHAR NOT NULL
);

Notice that we have defined a PRIMARY KEY constraint on the SubjectId column. The primary key constraint won’t allow two duplicated values to be inserted into the SubjectId column so that all the values in that column should be unique. Also, notice that we choose a conflict resolution to be “IGNORE“.

The command should run successfully and you shouldn’t get any errors:

ON CONFLICT IGNORE Example

Step 2) Now, let’s insert some values into the new table subjects, but with a value that violates the primary key constraint:

INSERT INTO Subjects VALUES(1, 'Algebra');
INSERT INTO Subjects VALUES(2, 'Database Course');
INSERT INTO Subjects VALUES(2, 'Data Structures');
INSERT INTO Subjects VALUES(4, 'Algorithms');

In these INSERT statement, we tried to insert two courses with the same Primary Key Subject id 2, which is a violation for the primary key constraint.

The commands should run fine and you shouldn’t get any errors. As following:

ON CONFLICT IGNORE Example

Step 3) Select all the subjects from the table as follows:

SELECT * FROM Subjects;

This will give you the list of subjects:

ON CONFLICT IGNORE Example

Notice that only three subjects were inserted “Algebra, Database Course, and Algorithms” instead of 4 rows.

The row that has the value that violates the primary key constraint, which is “Data Structures” was ignored and not inserted. However, SQLite continues executing other statements after that row.

Step 4) DELETE the table subjects to create it again with a different ON CONFLICT clause for the following example by running the following command:

DROP TABLE Subjects;

The drop command deletes the entire table. Table Subjects now doesn’t exist.

ON CONFLICT REPLACE Example

Step 1) Create a new table subject as follows:

CREATE TABLE [Subjects] (  
    [SubjectId] INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE,
    [SubjectName] NVARCHAR NOT NULL
);

Notice that we defined a PRIMARY KEY constraint on the SubjectId column. The primary key constraint won’t allow two duplicated values to be inserted into the SubjectId column so that all the values in that column should be unique.

Also, notice that we choose a conflict resolution option to be “REPLACE“. The command should run successfully and you shouldn’t get any errors:

ON CONFLICT REPLACE Example

Step 2) Now, let’s insert some values into the new table Subjects, but with a value that violates the primary key constraint:

INSERT INTO Subjects VALUES(1, 'Algebra');
INSERT INTO Subjects VALUES(2, 'Database Course');
INSERT INTO Subjects VALUES(2, 'Data Structures');
INSERT INTO Subjects VALUES(4, 'Algorithms');

In these INSERT statement, we tried to insert two courses with the same Primary Key Subject id 2, which is a violation for the primary key constraint.

The commands should run fine and you shouldn’t get any errors. As following:

ON CONFLICT REPLACE Example

Step 3) Select all the subjects from the table as follows:

SELECT * FROM Subjects;

This will give you the list of subjects:

ON CONFLICT REPLACE Example

Notice that only three subjects were inserted “Algebra, Data Structures, and Algorithms” whereas we tried to inserted 4 rows.

The row that has the value that violates the primary key constraint, which is “Data Structures” replaced the value “Database Course” as following:

  • The first two insert statements run fine with no problem. Two subjects Algebra, and Database Course will be inserted with ids 1, 2.
  • When SQLite tries to run the third insert statement with SubjectId 2 and SubjectName “Data Structures“, it finds out that there is already a subject with SubjectId = 2. Which is a violation for the primary key constraint defined on the SubjectId column.
  • SQLite will choose a REPLACE resolution for this conflict. It replaces the value that already exists in the subjects table with the new value from the insert statement. So, the “Database Course” SubjectName will be replaced with “Data Structures” SubjectName.

Summary

INSERT, UPDATE, and DELETE clauses are used to modify the data in the SQLite database. The CONFLICT clause is a powerful clause to resolve any conflict between the data and the data to modify.