CODEWITHSUNDEEP
androidsqlitesql-updatebulkupdate
JPM
JPM

Reputation: 9297

Bulk Update Database

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
}

Upvotes: 3

Views: 7226

Answers (3)

Vinay George Roy
Vinay George Roy

Reputation: 61

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();
}

Upvotes: 2

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

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.

Upvotes: 7

CommonsWare
CommonsWare

Reputation: 1006604

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

Upvotes: 8

Related Questions