I want to delete rows which satisfy any of multiple conditions.
For example, I pass a list of IDs, and I want to delete all rows with these IDs (IDs are unique).
This would be:
String[] ids = {"0", "1", "2", "3",...};
database.delete("rows" , "id=? OR id=? OR id=? OR id=? OR ..." , ids );
Is there any way to do it compact without multiple OR?
You may get it done through db.execSQL method and SQL's IN keyword. For example:
String args = TextUtils.join(", ", ids);
db.execSQL(String.format("DELETE FROM rows WHERE ids IN (%s);", args));
1) OR (1=1 ...
... WHERE ids IN (?) and then args should be passed as the second argument to execSQL()What you want to use is an IN clause, something like (with 4 IDs);
database.delete("rows", "id IN (?, ?, ?, ?)", ids );
Upon request, this is (one example of) how you can do it dynamically;
database.delete("rows",
"id IN (" + new String(new char[ids.length-1]).replace("\0", "?,") + "?)",
ids);
Android official documentation tells here that using execSQL is not the proper way to delete records.
I would like to suggest the following simple method. Using provided delete() api.
String[] idArray = new String[] {"1", "2", "3"};
String idsCSV = TextUtils.join(",", idArray);
SQLiteDatabase db = getWritableDatabase();
if (db != null) {
db.delete("table_name", "id IN (" + idsCSV + ")", null);
db.close();
}
You could use id IN (1, 2, 3, 4, ...) and directly print your array values inside the brackets:
database.delete("rows", String.format("id IN (%s)", StringUtils.join(ids, ",")));
As an alternative, I'd try to use some kind of flags column for such things (if there's something like being able to flag single entries for deletion; I don't know how your ID list is "built").
StringUtils.join().
delete(), you'll need to add new String [] {}.Here's an example which builds the "?, ?, ?, ..." placeholder string with StringBuilder. If there are many parameters, plain string concatenation would create lots of garbage, StringBuilder helps with that.
String[] ids = {"0", "1", "2", "3",...};
StringBuilder placeholders = new StringBuilder();
for (int i = 0; i < ids.length; i++) {
if (i != 0)
placeholders.append(", ");
placeholders.append("?");
}
String where = "id IN (" + placeholders.toString() + ")";
db.delete("rows", where, args);
I have done this using this :
Sqlite Statement Syntax :
db.delete(TABLE_NAME,"COLUMN IN (?)", new String[]{commaSaparatedString})
Example based on question :
String args = TextUtils.join(", ", ids);
db.delete("rows","id IN (?)", new String[]{args})
db.delete returned 0 = no rows got deleted.
This work for my varchar ID.
public Integer deleteObjects(List<Object> objects){
if (objects.size()<1) return 0;
SQLiteDatabase db = getWritableDatabase();
StringBuilder strb= new StringBuilder();
for(int i = 0; i<objects.size();i++){
strb.append("'");
strb.append(objects.get(i).getId().toString());
strb.append("'");
// last element without comma
if (i<objects.size()-1) strb.append(",");
}
int rows = db.delete("table_name","id IN (" + strb + ")",null);
db.close();
return rows;
}
This build WHERE string with args directly. Equivalent to:
db.delete(TABLE_NAME,"id IN ('id0-maddog','id1-foo','id2-bar')",null);
👌
