SQLite - UPSERT *not* INSERT or REPLACE

Question

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 determin 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 doesnt 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 asusme 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?

Answer 1

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.

Answer 2

You can indeed do an upsert in SQLite, it just looks a little different than you are used to. It would look something like:

INSERT INTO table name (column1, column2) 
VALUES ("value12", "value2") WHERE id = 123 
ON CONFLICT DO UPDATE 
SET column1 = "value1", column2 = "value2" WHERE id = 123

Answer 3

I think this may be what you are looking for: ON CONFLICT clause.

If you define your table like this:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    field1 TEXT 
); 

Now, if you do an INSERT with an id that already exists, SQLite automagically does UPDATE instead of INSERT.

Hth...

Answer 4

This method remixes a few of the other methods from answer in for this question and incorporates the use of CTE (Common Table Expressions). I will introduce the query then explain why I did what I did.

I would like to change the last name for employee 300 to DAVIS if there is an employee 300. Otherwise, I will add a new employee.

Table Name: employees Columns: id, first_name, last_name

The query is:

INSERT OR REPLACE INTO employees (employee_id, first_name, last_name)
WITH registered_employees AS ( --CTE for checking if the row exists or not
    SELECT --this is needed to ensure that the null row comes second
        *
    FROM (
        SELECT --an existing row
            *
        FROM
            employees
        WHERE
            employee_id = '300'

        UNION

        SELECT --a dummy row if the original cannot be found
            NULL AS employee_id,
            NULL AS first_name,
            NULL AS last_name
    )
    ORDER BY
        employee_id IS NULL --we want nulls to be last
    LIMIT 1 --we only want one row from this statement
)
SELECT --this is where you provide defaults for what you would like to insert
    registered_employees.employee_id, --if this is null the SQLite default will be used
    COALESCE(registered_employees.first_name, 'SALLY'),
    'DAVIS'
FROM
    registered_employees
;

Basically, I used the CTE to reduce the number of times the select statement has to be used to determine default values. Since this is a CTE, we just select the columns we want from the table and the INSERT statement uses this.

Now you can decide what defaults you want to use by replacing the nulls, in the COALESCE function with what the values should be.

Answer 5

The best approach I know is to do an update, followed by an insert. The "overhead of a select" is necessary, but it is not a terrible burden since you are searching on the primary key, which is fast.

You should be able to modify the below statements with your table & field names to do what you want.

--first, update any matches
UPDATE DESTINATION_TABLE DT
SET
  MY_FIELD1 = (
              SELECT MY_FIELD1
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
 ,MY_FIELD2 = (
              SELECT MY_FIELD2
              FROM SOURCE_TABLE ST
              WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY
              )
WHERE EXISTS(
            SELECT ST2.PRIMARY_KEY
            FROM
              SOURCE_TABLE ST2
             ,DESTINATION_TABLE DT2
            WHERE ST2.PRIMARY_KEY = DT2.PRIMARY_KEY
            );

--second, insert any non-matches
INSERT INTO DESTINATION_TABLE(
  MY_FIELD1
 ,MY_FIELD2
)
SELECT
  ST.MY_FIELD1
 ,NULL AS MY_FIELD2  --insert NULL into this field
FROM
  SOURCE_TABLE ST
WHERE NOT EXISTS(
                SELECT DT2.PRIMARY_KEY
                FROM DESTINATION_TABLE DT2
                WHERE DT2.PRIMARY_KEY = ST.PRIMARY_KEY
                );