SQLite INSERT, UPDATE, DELETE Query with Example

Details

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.

In this tutorial, you will learn-

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 Query - INSERT, UPDATE, DELETE

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 Query - INSERT, UPDATE, DELETE

This will insert two students:

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 Query - INSERT, UPDATE, DELETE

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 Query - INSERT, UPDATE, DELETE

As following:

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 Query - INSERT, UPDATE, DELETE

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

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 Query - INSERT, UPDATE, DELETE

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 Query - INSERT, UPDATE, DELETE

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 Query - INSERT, UPDATE, 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 Query - INSERT, UPDATE, 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:

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 Query - INSERT, UPDATE, DELETE

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:

SQLite Query - INSERT, UPDATE, DELETE

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:

SQLite Query - INSERT, UPDATE, DELETE

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

SELECT * FROM Subjects;

This will give you the list of subjects:

SQLite Query - INSERT, UPDATE, DELETE

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:

SQLite Query - INSERT, UPDATE, DELETE

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:

SQLite Query - INSERT, UPDATE, DELETE

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

SELECT * FROM Subjects;

This will give you the list of subjects:

SQLite Query - INSERT, UPDATE, DELETE

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:

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.

 

YOU MIGHT LIKE: