Bulk Update Database

Question

I've been looking on this site for a while but have not found the answer. I am trying to do a bulk update on data that I know is already in the table. I have one column that needs to be set when a certain condition comes back for the row ID. Here is the single method but I want to make this more efficient and do it as a bulk. Our database is not in a Provider so I just using a Helper class.

public void markUnavailable(int myId) {
    SQLiteDatabase db = this.getWritableDatabase();
    String sql = "UPDATE " + MYTABLE + " SET " + Col.IS_AVAILABLE + "= 0"+ " WHERE " + Col.MY_ID + "=" + myId;
    db.execSQL(sql);
    db.close();
}

I would like to pass in an array of myIds to do the bulk Update. I can't do a Insert or Replace statement because I don't have access to all the column data and don't want to pass this through due to too many codes changes.

public void markUnavailable(int[] myId) {
   // ????
   /// need some way to loop through and update in bulk
}

score 8 · Accepted Answer · answered Oct 10 '13 at 20:51

8

Try UPDATE tablename SET column=0 WHERE ID IN (...), where ... is a comma-delimited list of ID values.

answered Oct 10 '13 at 20:51

CommonsWare

986,068
189
2,389
2,491

This would require parsing array into string and an arbitrary length array may prevent SQL statement to compile. – LS_ᴅᴇᴠ Oct 11 '13 at 10:04
@LS_dev: Then do one `UPDATE` for every, say, 100 entries. – CommonsWare Oct 11 '13 at 10:18
Even more parsing needed. I don't think that should be a normal database approach. – LS_ᴅᴇᴠ Oct 11 '13 at 10:29
@LS_dev: You are welcome to your opinion. My opinion is that this solution will not be significantly more complex than yours from a coding standpoint, yet will have better performance characteristics by having ~1% of the database statements. That being said, I have not run a benchmark to see how much faster SQLite would handle 100 items in an `IN` than 100 individual `UPDATE` statements. – CommonsWare Oct 11 '13 at 10:38

score 7 · Answer 2 · answered Oct 11 '13 at 10:01

I'm not an Android developer, but according to good database practices, you should:

public void markUnavailable(int[] myId) { 
    SQLiteDatabase db = this.getWritableDatabase();
    db.beginTransaction();
    SQLiteStatement upd=db.compileStatement("UPDATE "+MYTABLE+" SET "+Col.IS_AVAILABLE+"=0 WHERE "+Col.MY_ID+"=?";
    for (int i = 0; i < myId.length; i++) { 
        upd.bindLong(1, myId[i]);
        upd.execute();
    }
    db.endTransaction();
}

Android has SQLiteDatabase.update would be very usefull in this case, but String [] whereArgs would not deal well with your int[] myId.

score 2 · Answer 3 · edited Oct 10 '17 at 19:43

The fastest way to do a bulk update would be to do it as a single transaction,by using begin and end transactions. Also if the size of the database is large it will be a good idea to make myID as the primary key of the table as it will significantly increase the speed of the speed in fetching the rows for update when the WHERE clause is used.[It is said that indexing can reduce the speed of update and insert but when the where clause is used,indexing has always increased my speed by huge margins.

public void markUnavailable(int[] myId) { 
  SQLiteDatabase db = this.getWritableDatabase();
  db.beginTransaction();
  SQLiteStatement upd=db.compileStatement("UPDATE "+MYTABLE+" SET "+Col.IS_AVAILABLE+"=0 WHERE "+Col.MY_ID+"=?");
  for (int i = 0; i < myId.length; i++) { 
      upd.clearBindings();
      upd.bindLong(1, myId[i]); // this matches the first "?"
      upd.execute();
  }
  db.setTransactionSucessful();
  db.endTransaction();
}

Bulk Update Database

3 Answers3