Deleting multiple rows using IN sql statement in whereClause

Question

I have a list that performs multiple selections then delete the items that are selected using this method.

 public Integer deleteDesc(ArrayList<String> rows) {
    String[] args = rows.toArray(new String[rows.size()]);
    Log.i("AMS", "Args: " + args);
    db = this.getWritableDatabase();
    return db.delete("DropdownList", "Description IN (?)", args);
}

where the parameter ArrayList contains the items that were selected. I works when I only select one item but returns an error "bind or column index out of range".

I'm pretty sure it's the whereClause which cause it because I'm not sure how to correctly use the "?"

to use `IN` as a condition clause in sqlite/android number of `?` should match number of args, ex, if you have args [1,4,6] then the condition clause should be `"Description IN (?,?,?)"` — Yazan, Jul 23 '17 at 06:41
@Yazan Feel free to post an answer which handles an arbitrary number of parameters gracefully. — Tim Biegeleisen, Jul 23 '17 at 06:45
@TimBiegeleisen , i think it's not appropriate, as Elbert John Felipe did it but using `TextUtils.join()` and you did similar (dynamic args count) using native code, so i think my answer would be redundant, i thought it would be better if someone modify there answer, and make it closer to OP request, but **for sure** it's up to you, i can only suggest — Yazan, Jul 23 '17 at 06:49

score 0 · Answer 1 · answered Jul 23 '17 at 06:04

0

I used this statement TextUtils.join(",", Collections.nCopies(args.length, "?"));

working code:

 public Integer deleteDesc(ArrayList<String> rows) {
    String[] args = rows.toArray(new String[rows.size()]);
    db = this.getWritableDatabase();
    return db.delete("DropdownList", "Description IN (" + TextUtils.join(",", Collections.nCopies(args.length, "?"))
            + ")", args);
}

answered Jul 23 '17 at 06:04

Elbert John Felipe

181
1
13

This does not use prepared statements and is therefore prone to SQL injection. – Tim Biegeleisen Jul 23 '17 at 06:10

Tim Biegeleisen · Answer 2 · 2017-07-23T06:15:26.083

0

You can build the WHERE IN clause using ? placeholders for each value using a prepared statement:

SQLiteDatabase db = this.getWritableDatabase();
StringBuilder sql = new StringBuilder("DELETE FROM yourTable WHERE Description IN (?");
for (int i=1; i < args.length; i++) {
    sql.append(",?");
}
sql.append(")");
SQLiteStatement stmt = db.compileStatement(sql.toString());
for (int i=0; i < args.length; ++i) {
    stmt.bindString(i+1, args[i]);
}
stmt.execute();

Note that using a prepared statement here is probably highly recommended, since you are deleting data. Allowing a SQL injection in this method could have bad side effects.

edited Jul 23 '17 at 06:15

answered Jul 23 '17 at 06:09

Tim Biegeleisen

502,043
27
286
360

actually, you still can use the `db.delete()` method, with the `where clause` param generated dynamically `..."Description IN (?,?,?)", args);` **Note: ? count = args.length** – Yazan Jul 23 '17 at 06:42

Deleting multiple rows using IN sql statement in whereClause

2 Answers2