SQLite – UPSERT *not* INSERT or REPLACE

sqlsqliteupsert

http://en.wikipedia.org/wiki/Upsert

Insert Update stored proc on SQL Server

Is there some clever way to do this in SQLite that I have not thought of?

Basically I want to update three out of four columns if the record exists,
If it does not exists I want to INSERT the record with the default (NUL) value for the fourth column.

The ID is a primary key so there will only ever be one record to UPSERT.

(I am trying to avoid the overhead of SELECT in order to determine if I need to UPDATE or INSERT obviously)

Suggestions?

I cannot confirm that Syntax on the SQLite site for TABLE CREATE.
I have not built a demo to test it, but it doesn't seem to be supported.

If it was, I have three columns so it would actually look like:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    Blob1 BLOB ON CONFLICT REPLACE, 
    Blob2 BLOB ON CONFLICT REPLACE, 
    Blob3 BLOB 
);

but the first two blobs will not cause a conflict, only the ID would
So I assume Blob1 and Blob2 would not be replaced (as desired)

UPDATEs in SQLite when binding data are a complete transaction, meaning
Each sent row to be updated requires: Prepare/Bind/Step/Finalize statements
unlike the INSERT which allows the use of the reset function

The life of a statement object goes something like this:

  1. Create the object using sqlite3_prepare_v2()
  2. Bind values to host parameters using sqlite3_bind_ interfaces.
  3. Run the SQL by calling sqlite3_step()
  4. Reset the statement using sqlite3_reset() then go back to step 2 and repeat.
  5. Destroy the statement object using sqlite3_finalize().

UPDATE I am guessing is slow compared to INSERT, but how does it compare to SELECT using the Primary key?

Perhaps I should use the select to read the 4th column (Blob3) and then use REPLACE to write a new record blending the original 4th Column with the new data for the first 3 columns?

Best Answer

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.

enter image description here

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')
          );
Related Topic