title: Android/SQLite: Insert-Update table columns to keep the identifier
body: <p>Currently, I am using the following statement to create a table in an SQLite database on an Android device.</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS 'locations' (
    '_id' INTEGER PRIMARY KEY AUTOINCREMENT, 'name' TEXT, 
    'latitude' REAL, 'longitude' REAL, 
    UNIQUE ( 'latitude',  'longitude' ) 
  ON CONFLICT REPLACE );
  </code></pre>
  
  <p>The conflict-clause at the end causes that rows are <strong>dropped</strong> when new inserts are done that come with the same coordinates. The <a href="http://www.sqlite.org/lang_conflict.html" rel="nofollow noreferrer">SQLite documentation</a> contains further information about the conflict-clause.</p>
  
  <p>Instead, I would like to <strong>keep</strong> the former rows and just <strong>update</strong> their columns. What is the most efficient way to do this in a Android/SQLite environment?</p>
  
  <ul>
  <li>As a conflict-clause in the <code>CREATE TABLE</code> statement.</li>
  <li>As an <code>INSERT</code> trigger.</li>
  <li>As a conditional clause in the <code>ContentProvider#insert</code> method.</li>
  <li>... any better you can think off</li>
  </ul>
  
  <p>I would think it is more performant to handle such conflicts within the database. Also, I find it hard to rewrite the <code>ContentProvider#insert</code> method to consider the <em>insert-update</em> scenario. Here is code of the <code>insert</code> method:</p>
  
  <pre><code>public Uri insert(Uri uri, ContentValues values) {
      final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
      long id = db.insert(DatabaseProperties.TABLE_NAME, null, values);
      return ContentUris.withAppendedId(uri, id);
  }
  </code></pre>
  
  <p>When data arrives from the backend all I do is inserting the data as follows.</p>
  
  <pre><code>getContentResolver.insert(CustomContract.Locations.CONTENT_URI, contentValues);
  </code></pre>
  
  <p>I have problems figuring out how to apply an alternative call to <code>ContentProvider#update</code> here. Additionally, this is not my favored solution anyways.</p>
  
  <hr>
  
  <p><strong>Edit:</strong></p>
  
  <p>@CommonsWare: I tried to implement your suggestion to use <code>INSERT OR REPLACE</code>. I came up with this ugly piece of code.</p>
  
  <pre><code>private static long insertOrReplace(SQLiteDatabase db, ContentValues values, String tableName) {
      final String COMMA_SPACE = ", ";
      StringBuilder columnsBuilder = new StringBuilder();
      StringBuilder placeholdersBuilder = new StringBuilder();
      List&lt;Object&gt; pureValues = new ArrayList&lt;Object&gt;(values.size());
      Iterator&lt;Entry&lt;String, Object&gt;&gt; iterator = values.valueSet().iterator();
      while (iterator.hasNext()) {
          Entry&lt;String, Object&gt; pair = iterator.next();
          String column = pair.getKey();
          columnsBuilder.append(column).append(COMMA_SPACE);
          placeholdersBuilder.append("?").append(COMMA_SPACE);
          Object value = pair.getValue();
          pureValues.add(value);
      }
      final String columns = columnsBuilder.substring(0, columnsBuilder.length() - COMMA_SPACE.length());
      final String placeholders = placeholderBuilder.substring(0, placeholdersBuilder.length() - COMMA_SPACE.length());
      db.execSQL("INSERT OR REPLACE INTO " + tableName + "(" + columns + ") VALUES (" + placeholders + ")", pureValues.toArray());
  
      // The last insert id retrieved here is not safe. Some other inserts can happen inbetween.
      Cursor cursor = db.rawQuery("SELECT * from SQLITE_SEQUENCE;", null);
      long lastId = INVALID_LAST_ID;
      if (cursor != null &amp;&amp; cursor.getCount() &gt; 0 &amp;&amp; cursor.moveToFirst()) {
          lastId = cursor.getLong(cursor.getColumnIndex("seq"));
      }
      cursor.close();
      return lastId;
  }
  </code></pre>
  
  <p>When I check the SQLite database, however, equal columns are <strong>still removed and inserted with new ids</strong>. I do not understand why this happens and thought the reason is my conflict-clause. But the <a href="http://sqlite.org/lang_conflict.html" rel="nofollow noreferrer">documentation</a> states the opposite.</p>
  
  <blockquote>
    <p>The algorithm specified in the OR clause of an INSERT or UPDATE
    <strong>overrides</strong> any algorithm specified in a CREATE TABLE. If no algorithm
    is specified anywhere, the ABORT algorithm is used.</p>
  </blockquote>
  
  <p>Another disadvantage of this attempt is that you loose the value of the id which is return by an insert statement. To compensate this, I finally found an option to ask for the <code>last_insert_rowid</code>. It is as explained <a href="https://stackoverflow.com/a/4018593/356895">in the posts of dtmilano and swiz</a>. I am, however, not sure if this is safe since another insert can happen inbetween.</p>