SQLite Upsert Examples

The “insert” statement is used to add new records into the database and the “update” statement is used to update any existing records in the database table. The “upsert” statement is added to the SQLite database from the 3.24.0 version. This statement is used to insert or update the record of the SQLite database table. The methods of using the “upsert” statement are shown in this tutorial.

Prerequisite:

You have to complete the following tasks before practicing the SQLite statements of this tutorial:

Run the following command from the terminal to create an SQLite file named “company.db” that contains the database:

sqlite3 company.db

Run the following SQLite statement to create a table named “products”. The table contains five fields, one primary key, and one unique key:

CREATE TABLE books (

id INTEGER PRIMARY KEY,

title TEXT NOT NULL UNIQUE,

author TEXT NOT NULL,

publication TEXT NOT NULL,

price INTEGER NOT NULL);

Run the following commands to format the output and check the structure of the “books” table:

.header on

.mode column

pragma table_info('books');

Run the following SQLite statements to insert some records into the “books” table and check whether the records are inserted properly or not:

INSERT INTO books (id, title, author, publication, price)

VALUES

(1001, 'Learning SQLite for iOS', 'Gene Da Rocha', 'Packt Publishing', 56),

(1002, 'SQLite Essentials', 'Sunny Kumar Aditya', 'Packt Publishing', 45),

(1003, 'The Definitive Guide to SQLite', 'Mike Owens', 'Apress', 39),

(1004, 'Using SQLite', 'Jay A. Kreibich', "O'Reilly Media", 50);

SELECT * FROM books;

If no error exists in the INSERT query and the SELECT query, the following output appears:

Different Examples of the Upsert Statement

The uses of the “upsert” statement are shown in the following in this part of the tutorial:

Example 1: Using the UPSERT Statement to Do Nothing on UPDATE

Run the following “upsert” statement that does nothing if any conflict appears at the time of inserting the record. Next, run the SELECT statement to check whether any new record is added or not:

INSERT INTO books VALUES (1004, 'The SQL Guide to SQLite', 'Rick F. van der Lans', 'Lulu.com', 50) ON CONFLICT do nothing;

SELECT * FROM books;

The “books” table contains a record with the “id” value of 1004 earlier. So, the insertion conflict occurs at the time of executing the query and no new record is inserted into the table.

Example 2: Using the UPSERT Statement to Update a Single Field on UPDATE

Run the following “upsert” statement that updates the price value of the “books” table if any conflict appears at the time of inserting the record. Next, run the SELECT statement to check whether any new record is added or any existing record is updated:

INSERT INTO books VALUES (1004, 'The SQL Guide to SQLite', 'Rick F. van der Lans', 'Lulu.com', 50) ON CONFLICT do UPDATE SET price=40;

SELECT * FROM books;

According to the previous example, there is a record with the ID value of 1004 in the “books” table and the price value of this record is 50 before executing the “upsert” statement. The price value changed to 40 for the update query of the “upsert” statement. You can update multiple fields of the table on conflict using the “upsert” statement that is shown in the next example.

Example 3: Using the UPSERT Statement to Update Multiple Fields on UPDATE

Run the following “upsert” statement that updates the title, author, and publication values of the “books” table if any conflict appears at the time of inserting the record. Next, run the SELECT statement to check whether any new record is added or any existing record is updated.

INSERT INTO books VALUES (1004, 'The SQL Guide to SQLite', 'Rick F. van der Lans', 'Lulu.com', 50) ON CONFLICT do UPDATE SET title='SQLite', author='Chris Newman', publication='Sams';

SELECT * FROM books;

A record with the ID value of 1004 exists in the “books” table. So, the insertion conflict appears and the values of the “title”, “author”, and “publication” fields have are changed with the “SQLite”, “Chris Newman”, and “Sams” values where the “id” value is 1004.

Example 4: Using the UPSERT Statement to Insert a Record for No Conflict

Run the following “upsert” statement that inserts a new record if no conflict occurs. Next, run the SELECT statement to check if any new record is added or not.

INSERT INTO books VALUES (1005, 'Learning SQLite', 'Rick', 'apress', 55) ON CONFLICT do nothing;

SELECT * FROM books;

There is no record with the “id” value of 1005 in the “books” table before. So, a new record is inserted into the table.

Conclusion

The “upsert” statement is very useful if you want to insert a new data or modify the existing records with the new data by keeping the primary key value unchanged. The examples of this tutorial will help the SQLite users to know the uses of the “upsert” statement properly.