SQLite - UPSERT *不* INSERT或REPLACE(SQLite - UPSERT *not* INSERT or REPLACE)

 http://en.wikipedia.org/wiki/Upsert 
 
 在SQL Server上插入更新存储的proc 
 
 在SQLite中有没有一些聪明的方法，我没有想到？ 
 
 基本上，如果记录存在，我想更新四列中的三个，如果不存在，我想使用第四列的默认（NUL）值插入记录。 
 
 该ID是一个主要的关键，所以UPSERT只会有一个记录。 
 
 （我试图避免SELECT的开销，以确定是否需要UPDATE或INSERT显然） 
 
 建议？ 

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:
 
 
 
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?

原文：https://stackoverflow.com/questions/418898