Sqlite UPDATE with parameterised IN (...) clause

Question

In my app I'm working with sqlite database - and I hit a strange issue.

I have a table that looks like this:

_id   field1   field2   status
---   ------   ------   ------
 1    value    value    ...
 2    value    value    ...
...

At one place, I need to update some rows to set them to another status, so I'm trying

SQLiteDatabase db = getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("status", STATUS_SENT);
Set<Integer> ids = datasource.getCompletedIds();
String idparam = TextUtils.join(",", ids);
int cnt = db.update(TABLE_ORDERS, cv, "_id in (?)", new String[] { idparam });
Log.d("db", "updated " + cnt + " rows");
db.close();

However nothing gets updated - and db.update returns 0. What am I missing?

It took me a minute to find since "in" is a [**horrible** search engine parameter](http://stackoverflow.com/search?q=[android]+[sqlite]+in)... but: [Android/SQLite IN clause and placeholders](http://stackoverflow.com/q/7418849/1267661). — Sam, Feb 07 '13 at 18:32

score 5 · Accepted Answer · answered Feb 07 '13 at 19:02

5

I'm not happy recommending not using parameters, but it this case, it's actually easier than formatting all those ? markers (and with plain integers, just as safe):

db.update(TABLE_ORDERS, cv, "_id IN (" + idparam + ")", null);

answered Feb 07 '13 at 19:02

CL.

173,858
17
217
259

score 1 · Answer 2 · answered Feb 07 '13 at 18:29

1

Unfortunately you must list one insertion character ? for each id in your Set...
A popular fix is to write a quick utility to add the appropriate number of ?s based on the Set's size.

answered Feb 07 '13 at 18:29

Sam

86,580
20
181
179

Sqlite UPDATE with parameterised IN (...) clause

2 Answers2

Linked

Related