SQLite - UPSERT *not* INSERT or REPLACE

Question

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

http://stackoverflow.com/questions/13540/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?

Answer 1

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
                );

Answer 2

Very tricky. I forgot to mention this is binard BLOB data I am UPSERTing so I must bind it...

I would love to do this in one step, but I cannot see a way. If I must use two queries i might as well just do the SELECT and then choose INSERT or REPLACE?

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

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.

Answer 5

Mosor,

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)

Sambo, 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 6

Having just read this thread and been disappointed that it wasn't easy to just to this "UPSERT"ing, I investigated further...

You can actually do this directly and easily in SQLITE.

Instead of using" "INSERT INTO"

Use: "INSERT OR REPLACE INTO"

This does exactly what you want it to do!