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.