In SQLite, you can do an UPSERT operation in the following ways:
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 | +---------------+----------------------+
This post was published (and was last revised ) by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.