Update/replace row if same ID already exists

Question

I have the below method which inserts values in my sqlite db. I'm trying to update the code to handle situations where "carid" and "sellerno" already exist in the table and if they do to replace with the new values being inserted. Any help is appreciated.

public void addListItem(String carid,String sellerno,String condition,String dat) {

  SQLiteDatabase db = this.getWritableDatabase();

  ContentValues values = new ContentValues();
  values.put(carid, carid);
  values.put(sellerno, sellerno);
  values.put(cond, condition);
  values.put(updatetime, dat);
  db.insert(TABLE_CARS, null, values);
  db.close(); 
}

There is some longer discussions here: http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace — eckes, Aug 23 '15 at 00:01
When you create your table can set un conflict update or replace. As the answer by karakury xplain. — Max Pinto, Aug 23 '15 at 01:53

Karakuri · Accepted Answer · 2017-01-27T14:43:17.187

There are two requirements:

SQLite needs to know that the combination of carid and sellerno must be unique for all rows in the table.
SQLite needs to know what to do when an insert or update results in a conflict (more than one row with the same combination of values for those columns).

You can do both of these at once by modifying your CREATE TABLE command as follows:

CREATE TABLE tableName (
    column1 ...,
    column2 ...,
    ...,
    UNIQUE(column1, column2) ON CONFLICT REPLACE)

Now any insert/update will automatically replace the values in existing rows when the insert/update would create a conflict.

However, there may be situations where you do NOT want to replace the values in the row when there is a conflict. In those cases, you should specify the conflict algorithm in the insert/update itself (using this or this), which will override the replace algorithm specified above. Alternatively, you can leave off the ON CONFLICT REPLACE above and just use regular inserts/updates, but then you must insert/update with conflict when you want to replace.

You can read more about conflict algorithms here.

I replace raw successfully.. But i don't want to change its raw id(auto increment in create table). Then what should i do?? — Vishal Jadav, Jan 27 '17 at 10:42
@VishalJadav Then you need to do an update instead. If you don't know whether there is an existing row or not and want to perform _either_ an insert or update based on that, you will have to implement it differently. — Karakuri, Jan 27 '17 at 14:48

score 0 · Answer 2 · answered Aug 23 '15 at 01:03

The update code is very similar to what you have now.

You have to add the new values but the same ids that you want, and then instead of the method insert(...) you use replace(...) or update(...).

db.update(TABLE_CARS, values, "_id "+"="+1, null);
// The third argument above is the where clause.

The other way would be:

db.replace(TABLE_CARS, null, values);

The rest of your code is the same. Just change the insert line.

Update/replace row if same ID already exists

2 Answers2