How to INSERT or UPDATE in a Single SQLite Query?

In SQLite, you can do an UPSERT operation in the following ways:

• Using ON CONFLICT Clause;
• Using INSERT OR REPLACE.

Using either of these will allow you to perform an INSERT or UPDATE in a single query, based on whether a uniqueness constraint is violated or not.

Using ON CONFLICT Clause

Starting with SQLite v3.24.0, you can use the ON CONFLICT clause to perform an UPSERT, using the following syntax:

-- SQLite 3.24.0+
INSERT INTO table (`unique_col`) VALUES ('unique_val')
  ON CONFLICT (`unique_col`) DO UPDATE SET `some_col` = 'some_col_new_val'

In this UPSERT operation, the "ON CONFLICT" clause specifies the conflict target, which is the column (or columns) that must have a unique constraint or unique index defined for the UPSERT operation to work correctly.

For example, let's suppose you have the following "blog_post" table, with the "slug" column having the UNIQUE constraint:

CREATE TABLE `blog_post` (
  `title` VARCHAR(25) NOT NULL,
  `slug` VARCHAR(25) NOT NULL,
  `hits` INT(1) NOT NULL DEFAULT 1,
  CONSTRAINT `uq_slug` UNIQUE (`slug`)
);

INSERT INTO `blog_post` (`title`, `slug`) VALUES
  ('Lorem Ipsum', 'lorem-ipsum'),
  ('Proin ut Ante', 'prion-ut-ante'),
  ('Fusce vitae', 'fusce-vitae');

This would result in the following SQLite table:

+---------------+---------------+------+
| title         | slug          | hits |
+---------------+---------------+------+
| Lorem Ipsum   | lorem-ipsum   |  1   |
| Proin ut Ante | prion-ut-ante |  1   |
| Fusce vitae   | fusce-vitae   |  1   |
+---------------+----------------------+

When you use an UPSERT query like the following, it would perform an UPDATE when the INSERT violates the uniqueness constraint:

-- SQLite 3.24.0+
INSERT INTO `blog_post` (`title`, `slug`)
  VALUES ('Lorem Ipsum', 'lorem-ipsum')
  ON CONFLICT (`slug`) DO UPDATE SET hits = hits + 1;

This would result in "hits" column being incremented by 1 as the slug "lorem-ipsum" already exists:

+---------------+---------------+------+
| title         | slug          | hits |
+---------------+---------------+------+
| Lorem Ipsum   | lorem-ipsum   |  2   |
| Proin ut Ante | prion-ut-ante |  1   |
| Fusce vitae   | fusce-vitae   |  1   |
+---------------+----------------------+

When the uniqueness constraint does not fail, a new record is created. For example, the following query will result in a new record being added to the table:

-- SQLite 3.24.0+
INSERT INTO `blog_post` (`title`, `slug`)
  VALUES ('Foo bar', 'foo-bar')
  ON CONFLICT (`slug`) DO UPDATE SET hits = hits + 1;

After this query is executed, the table will look like the following:

+---------------+---------------+------+
| title         | slug          | hits |
+---------------+---------------+------+
| Lorem Ipsum   | lorem-ipsum   |  2   |
| Proin ut Ante | prion-ut-ante |  1   |
| Fusce vitae   | fusce-vitae   |  1   |
| Foo bar       | foo-bar       |  1   |
+---------------+----------------------+

Using INSERT OR REPLACE

You can use the following INSERT OR REPLACE syntax to perform an UPSERT:

INSERT OR REPLACE INTO `table` (`unique_col`, `some_col`)
  VALUES ('unique_val', COALESCE((
    SELECT 'some_col_new_val' FROM `table`
    WHERE `unique_col` = 'unique_val'
  ), 'some_column_default_val'));

In this syntax, using the COALESCE function allows you to specify two or more arguments, where the function returns the first non-null argument. This allows you to have the ability to set a default value in the event that the inner query does not return a match.

For example, let's suppose you have the following "blog_post" table, with the "slug" column having the UNIQUE constraint:

CREATE TABLE `blog_post` (
  `title` VARCHAR(25) NOT NULL,
  `slug` VARCHAR(25) NOT NULL,
  `hits` INT(1) NOT NULL DEFAULT 1,
  CONSTRAINT `uq_slug` UNIQUE (`slug`)
);

INSERT INTO `blog_post` (`title`, `slug`) VALUES
  ('Lorem Ipsum', 'lorem-ipsum'),
  ('Proin ut Ante', 'prion-ut-ante'),
  ('Fusce vitae', 'fusce-vitae');

This would result in the following SQLite table:

+---------------+---------------+------+
| title         | slug          | hits |
+---------------+---------------+------+
| Lorem Ipsum   | lorem-ipsum   |  1   |
| Proin ut Ante | prion-ut-ante |  1   |
| Fusce vitae   | fusce-vitae   |  1   |
+---------------+----------------------+

The following UPSERT query will return "1" for the "hits" column when the inner query does not return a match. Otherwise, it will increment the "hits" column:

INSERT OR REPLACE INTO `blog_post` (`title`, `slug`, `hits`) VALUES
  ('Lorem Ipsum', 'lorem-ipsum', COALESCE((
    SELECT `hits` + 1 FROM `blog_post`
    WHERE `slug` = 'lorem-ipsum'
  ), 1));

When this query is executed, it will result in "hits" column being incremented by 1 since the slug "lorem-ipsum" already exists:

+---------------+---------------+------+
| title         | slug          | hits |
+---------------+---------------+------+
| Lorem Ipsum   | lorem-ipsum   |  2   |
| Proin ut Ante | prion-ut-ante |  1   |
| Fusce vitae   | fusce-vitae   |  1   |
+---------------+----------------------+

When the uniqueness constraint does not fail, a new record is created. For example, the following query will result in a new record being added to the table:

INSERT OR REPLACE INTO `blog_post` (`title`, `slug`, `hits`) VALUES
  ('Foo bar', 'foo-bar', COALESCE((
    SELECT `hits` + 1 FROM `blog_post`
    WHERE `slug` = 'foo-bar'
  ), 1));

After this query is executed, the table will look like the following:

+---------------+---------------+------+
| title         | slug          | hits |
+---------------+---------------+------+
| Lorem Ipsum   | lorem-ipsum   |  2   |
| Proin ut Ante | prion-ut-ante |  1   |
| Fusce vitae   | fusce-vitae   |  1   |
| Foo bar       | foo-bar       |  1   |
+---------------+----------------------+