title: SQLite - UPSERT *not* INSERT or REPLACE
body: <p><a href="http://en.wikipedia.org/wiki/Upsert" rel="noreferrer">http://en.wikipedia.org/wiki/Upsert</a></p>
  <p><a href="https://stackoverflow.com/questions/13540/insert-update-stored-proc-on-sql-server">Insert Update stored proc on SQL Server</a></p>
  <p>Is there some clever way to do this in SQLite that I have not thought of?</p>
  <p>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.</p>
  <p>The ID is a primary key so there will only ever be one record to UPSERT.</p>
  <p>(I am trying to avoid the overhead of SELECT in order to determine if I need to UPDATE or INSERT obviously)</p>
  <p>Suggestions?</p>
  <hr />
  <p>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.</p>
  <p>If it was, I have three columns so it would actually look like:</p>
  <pre><code>CREATE TABLE table1( 
      id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
      Blob1 BLOB ON CONFLICT REPLACE, 
      Blob2 BLOB ON CONFLICT REPLACE, 
      Blob3 BLOB 
  );
  </code></pre>
  <p>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)</p>
  <hr />
  <p>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</p>
  <p>The life of a statement object goes something like this:</p>
  <ol>
  <li>Create the object using sqlite3_prepare_v2()</li>
  <li>Bind values to host parameters using sqlite3_bind_ interfaces.</li>
  <li>Run the SQL by calling sqlite3_step()</li>
  <li>Reset the statement using sqlite3_reset() then go back to step 2 and repeat.</li>
  <li>Destroy the statement object using sqlite3_finalize().</li>
  </ol>
  <p>UPDATE I am guessing is slow compared to INSERT, but how does it compare to SELECT using the Primary key?</p>
  <p>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?</p>