SQLite - UPSERT *not* INSERT or REPLACE

Assuming three columns in the table: ID, NAME, ROLE

BAD: This will insert or replace all columns with new values for ID=1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

GOOD but tedious: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be the default (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

INSERT OR REPLACE is NOT equivalent to "UPSERT".

Say I have the table Employee with the fields id, name, and role:

INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")

Boom, you've lost the name of the employee number 1. SQLite has replaced it with a default value.

The expected output of an UPSERT would be to change the role and to keep the name.

Eric B’s answer is OK if you want to preserve just one or maybe two columns from the existing row. If you want to preserve a lot of columns, it gets too cumbersome fast.

Here’s an approach that will scale well to any amount of columns on either side. To illustrate it I will assume the following schema:

 CREATE TABLE page (
     id      INTEGER PRIMARY KEY,
     name    TEXT UNIQUE,
     title   TEXT,
     content TEXT,
     author  INTEGER NOT NULL REFERENCES user (id),
     ts      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

Note in particular that name is the natural key of the row – id is used only for foreign keys, so the point is for SQLite to pick the ID value itself when inserting a new row. But when updating an existing row based on its name, I want it to continue to have the old ID value (obviously!).

I achieve a true UPSERT with the following construct:

 WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) )
 INSERT OR REPLACE INTO page (id, name, title, content, author)
 SELECT old.id, new.name, new.title, old.content, new.author
 FROM new LEFT JOIN page AS old ON new.name = old.name;

The exact form of this query can vary a bit. The key is the use of INSERT SELECT with a left outer join, to join an existing row to the new values.

Here, if a row did not previously exist, old.id will be NULL and SQLite will then assign an ID automatically, but if there already was such a row, old.id will have an actual value and this will be reused. Which is exactly what I wanted.

In fact this is very flexible. Note how the ts column is completely missing on all sides – because it has a DEFAULT value, SQLite will just do the right thing in any case, so I don’t have to take care of it myself.

You can also include a column on both the new and old sides and then use e.g. COALESCE(new.content, old.content) in the outer SELECT to say “insert the new content if there was any, otherwise keep the old content” – e.g. if you are using a fixed query and are binding the new values with placeholders.

If you are generally doing updates I would ..

1. Begin a transaction
2. Do the update
3. Check the rowcount
4. If it is 0 do the insert
5. Commit

If you are generally doing inserts I would

1. Begin a transaction
2. Try an insert
3. Check for primary key violation error
4. if we got an error do the update
5. Commit

This way you avoid the select and you are transactionally sound on Sqlite.

This answer has be updated and so the comments below no longer apply.

2018-05-18 STOP PRESS.

UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0 (pending) !

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

alternatively:

Another completely different way of doing this is: In my application I set my in memory rowID to be long.MaxValue when I create the row in memory. (MaxValue will never be used as an ID you will won't live long enough.... Then if rowID is not that value then it must already be in the database so needs an UPDATE if it is MaxValue then it needs an insert. This is only useful if you can track the rowIDs in your app.