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)
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:
- Create the object using sqlite3_prepare_v2()
- Bind values to host parameters using sqlite3_bind_ interfaces.
- Run the SQL by calling sqlite3_step()
- Reset the statement using sqlite3_reset() then go back to step 2 and repeat.
- 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?