0

Is there a way to get last updated row id, as what we can get from insert operation?

Get generated id after insert

int row_id = -1

// fx_watchlist_id is having constraint unique (fx_watchlist_id) on conflict.
// So, we are pretty sure maximum updated row will be 1
int count = database.update(TABLE_TIMESTAMP, values, "fx_watchlist_id = ?", new String[]{Long.toString(fxWatchlistId)});
if (count <= 0) {
    row_id = database.insert(TABLE_TIMESTAMP, null, values);
} else {
    // Is there a way to retrieve last updated row id, without perform select?
    row_id = ...
}

return row_id;
Community
  • 1
  • 1
Cheok Yan Cheng
  • 47,586
  • 132
  • 466
  • 875

1 Answers1

1

The insert function returns the rowid because there is no other way of determining what value was automatically generated by the database.

Updates do not have this problem; the value of the whereClause parameter is already sufficient to find the records. Furthermore, updates might affect more or less than one record, so it is not possible to have a single return value; you need some kind of list for that, so you could just as well use a Cursor:

Cursor c = database.query(TABLE_TIMESTAMP, new String[] { "rowid" },
                          "fx_watchlist_id = " + fxWatchlistId, null,
                          null, null, null);
if (c.moveToFirst()) {
    row_id = c.getLong(0);
    database.update(TABLE_TIMESTAMP, values,
                    "rowid = " + row_id, null);
} else {
    row_id = database.insert(...);
}
CL.
  • 173,858
  • 17
  • 217
  • 259
  • The key here, if using this approach, is to query the-soon-to-be-updated rows *before* the actual update is performed as the `update` operation may very well alter the value of the fields in the `where` constraint, e.g. `UPDATE myTable SET col1=2 WHERE col1=1`. Querying with the original `where` clause (`col1=1`) *after* the update would in this case return an empty cursor. – dbm Aug 23 '16 at 06:43