CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Natix
Natix

Reputation: 14257

Delete multiple rows using IDs?

How can I delete multiple rows by a list of IDs in Android SQLite database?

I have defined a general delete method in this manner:

protected void deleteWhere(String whereClause, String[] whereArgs) {
    try {
        databaseHelper.getWritableDatabase().delete(
                getTableName(), whereClause, whereArgs);
    } finally {
        databaseHelper.close();
    }
}

And now I'm trying to call it with a list of IDs:

public void deleteAll(Iterable<T> entities) {
    Iterable<Long> ids = Iterables.transform(entities, getId);

    String whereClause = getIdColumn() + " IN (?)";
    String[] whereArgs = { TextUtils.join(",", ids) };
    deleteWhere(whereClause, whereArgs);
}

If the ID list contains for example the values [1, 2, 42], then I assume the resulting SQL should be:

DELETE FROM tableName WHERE _id IN (1,2,42);

But this doesn't seem to work correctly. If the list contains only 1 ID, then it is correctly deleted. However, if I provide multiple values, than zero rows are affected. What am I doing wrong?

Upvotes: 6

Views: 3967

Answers (3)

CL.
CL.

Reputation: 180300

When you give a single string as whereArgs, a single string ends up in the SQL command, as if you had written this:

... WHERE _id IN ('1,2,42')

This would compare each _id value against the value '1,2,42', which of course does not work.

If you use three parameter markers and give three strings in the whereArgs array, you would end up with three strings, like this:

... WHERE _id in ('1','2','42')

This works only when the _id column has integer affinity, which is true for a column declared as INTEGER PRIMARY KEY, but not in the general case.

The Android database API does not allow query parameters to have any type but string. When using integers, you should just insert them directly (as in ianhanniballake's answer):

String whereClause = getIdColumn() + " IN (" + TextUtils.join(",", ids) + ")";

Upvotes: 9

ianhanniballake
ianhanniballake

Reputation: 200130

You can't use whereArgs for IN statements due to the escaping done by whereArgs (unless you make a separate ? for each value) - instead, you have to embed the ids in your where statement:

String whereClause = getIdColumn() + " IN (" + TextUtils.join(",", ids) + ")";
deleteWhere(whereClause, null);

Upvotes: 4

eshayne
eshayne

Reputation: 935

Each "?" will only bind to a single value. So if your id list has three values, your whereClause would need to be "_id IN (?,?,?)"

Upvotes: 0

Related Questions