SQLite UPSERT

Summary: in this tutorial, you will learn how to perform upsert operations in SQLite using the ON CONFLICT clause of the INSERT statement.

Introduction to the SQLite upsert

Upsert is a combination of update and insert. Upsert inserts a new row if a specified unique identifier does not exist or updates an existing row if the same identifier already exists in a table.

Here’s the syntax for performing an upsert:

INSERT INTO table_name(column_list)
VALUES(value_list)
ON CONFLICT(conflict_column) 
DO 
   UPDATE SET column_name = expression
   WHERE conflict_condition;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• The INSERT INTO ... VALUES part defines the basic insert statement that inserts values into a specified column of a table.
• ON CONFLICT (conflict_colum) clause introduces the upsert behavior. If a conflict occurs in the conflict_column, it should take an action.
• DO marks the beginning of the conflict resolution strategy.
• UPDATE SET allows you to update existing rows when a conflict occurs. The optional WHERE clause defines which rows to update during a conflict. It is useful when you want to update only specific rows that meet the conflict_condition.

If you don’t want to take action when a conflict occurs, you can use the DO NOTHING strategy like this:

INSERT INTO table_name(column_list)
VALUES(value_list)
ON CONFLICT(conflict_column) 
DO NOTHING;Code language: SQL (Structured Query Language) (sql)

excluded keyword

If you attempt to insert or update a row that causes a unique constraint violation specified in the ON CONFLICT clause, the DO clause will take over.

The UPDATE clause following the DO keyword can use the excluded keyword to access the values you were trying to insert or update.

For example, if a row that you want to insert into a table is (name, email, phone), you can access the values of the row via the excluded keyword as follows:

excluded.name
excluded.email
excluded.phoneCode language: SQL (Structured Query Language) (sql)

SQLite upsert examples

Let’s take some examples of using the SQLite upsert.

1) Basic SQLite upsert example

First, create a table called search_stats to store the searched keywords and the number of searches:

CREATE TABLE search_stats(
   id INTEGER PRIMARY KEY,
   keyword TEXT UNIQUE NOT NULL,
   search_count INT NOT NULL DEFAULT 1   
);Code language: SQL (Structured Query Language) (sql)

Second, insert a row into the search_stats table:

INSERT INTO search_stats(keyword)
VALUES('SQLite');Code language: SQL (Structured Query Language) (sql)

Third, insert a row into the search_stats table with the same keyword. If the keyword already exists in the table, increase the search_count by one:

INSERT INTO search_stats(keyword)
VALUES ('SQLite')
ON CONFLICT (keyword)
DO 
   UPDATE 
   SET search_count = search_count + 1;Code language: SQL (Structured Query Language) (sql)

Fourth, retrieve data from the search_stats table:

SELECT * FROM search_stats;Code language: SQL (Structured Query Language) (sql)

Output:

id  keyword  search_count
--  -------  ------------
1   SQLite   2Code language: SQL (Structured Query Language) (sql)

Because the keyword SQLite already exists, the upsert updates the search_count value.

2) Selective Update on Conflict

First, create a new table called contacts to store the contact information:

CREATE TABLE IF NOT EXISTS contacts (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    phone TEXT NOT NULL,
    effective_date  DATE NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Next, insert a new row into the contacts table:

INSERT INTO contacts(name, email, phone, effective_date)
VALUES('Jane Doe', '[email protected]', '(408)-111-2222', '2024-04-05');Code language: SQL (Structured Query Language) (sql)

Then, verify the insert:

SELECT * FROM contacts;Code language: SQL (Structured Query Language) (sql)

Output:

id  name      email          phone           effective_date
--  --------  -------------  --------------  --------------
1   Jane Doe  [email protected]  (408)-111-2222  2024-04-05Code language: SQL (Structured Query Language) (sql)

After that, update the name, phone, and effective date if the email already exists and only update when the new effective date is later than the current effective date:

INSERT INTO
  contacts (name, email, phone, effective_date)
VALUES
  (
    'Jane Smith',
    '[email protected]',
    '(408)-111-3333',
    '2024-05-05'
  )
ON CONFLICT (email) DO
UPDATE
SET
  name = excluded.name,
  phone = excluded.phone,
  effective_date = excluded.effective_date
WHERE
  excluded.effective_date > contacts.effective_date;Code language: SQL (Structured Query Language) (sql)

Finally, verify the update:

SELECT * FROM contacts;Code language: SQL (Structured Query Language) (sql)

Output:

id  name        email          phone           effective_date
--  ----------  -------------  --------------  --------------
1   Jane Smith  [email protected]  (408)-111-3333  2024-05-05Code language: SQL (Structured Query Language) (sql)

Summary

• Upsert is a combination of insert and update.
• Upsert allows you to update an existing row or insert a new row if it doesn’t exist in the table.
• Use the excluded keyword to access the values you were trying to insert or update.