[sql] SQLite - UPSERT *not* INSERT or REPLACE

Here is a solution that really is an UPSERT (UPDATE or INSERT) instead of an INSERT OR REPLACE (which works differently in many situations).

It works like this:
1. Try to update if a record with the same Id exists.
2. If the update did not change any rows (NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0)), then insert the record.

So either an existing record was updated or an insert will be performed.

The important detail is to use the changes() SQL function to check if the update statement hit any existing records and only perform the insert statement if it did not hit any record.

One thing to mention is that the changes() function does not return changes performed by lower-level triggers (see http://sqlite.org/lang_corefunc.html#changes), so be sure to take that into account.

Here is the SQL...

Test update:

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY, 
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 2;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 2, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;

Test insert:

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY, 
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 3;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 3, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sqlite

getting " (1) no such column: _id10 " error Laravel: PDOException: could not find driver auto create database in Entity Framework Core How to open .SQLite files Accessing an SQLite Database in Swift When does SQLiteOpenHelper onCreate() / onUpgrade() run? Attempt to write a readonly database - Django w/ SELinux error Android sqlite how to check if a record exists How can I add the sqlite3 module to Python? "Insert if not exists" statement in SQLite

Examples related to upsert

PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL? SQLite UPSERT / UPDATE OR INSERT Insert into a MySQL table or update if exists Postgres: INSERT if does not exist already INSERT IF NOT EXISTS ELSE UPDATE? SQLite "INSERT OR REPLACE INTO" vs. "UPDATE ... WHERE" Insert, on duplicate update in PostgreSQL? How do I UPDATE a row in a table or INSERT it if it doesn't exist? SQLite - UPSERT *not* INSERT or REPLACE