0

I was wondering how do I update a table with IN in the WHERE clause. I can update it using = operator but not sure how I should do it with IN.

I tried to search a bit on google and SO but they seem to deal only with =.

Can anyone please guide me to some link or post an answer how to do it.

This is how I tried it.

 database.update("State_Regions",
            cvs, "Country_ID IN ( ? )  AND State_ID IN ( ? ) AND Region_ID IN ( ? )", 
            new String[]{countryIDStr, stateIDStr, regionsIDStr});

The arguments are a comma separated list of integers like 1,2,3...

Thank you

Umar Ali
  • 13
  • 6

1 Answers1

0

So you need multiple values for one ?, right? I don't think that's possible.

Each ? parameter will eventually be bound with [sqlite3_bind_XYZ](https://sqlite.org/c3ref/bind_blob.html, and there is no function there for binding multiple values (like an array) to one parameter.

There are two possible ways to work around it:

  1. Run the update once for every combination of args you want. This will get costlier the more different parameter combinations you have, but you can mitigate some of the overhead by wrapping it all in a transaction.
  2. Generate an SQL string with an appropriate number of question marks. If you do frequent updates with different numbers of parameters, this may impact performance by forcing repeated compilation of the query, since Android's compiled statement cache will not find a match when the number of parameters differ.

See also this question for the same case in Python.

Snild Dolkow
  • 6,669
  • 3
  • 20
  • 32
  • I have it like this countryIDStr = 1, 2, 3 stateIDStr = 1,3,4,5 And regionsIDStr = 1,3,5,63 So it is not possible? :( – Umar Ali Dec 20 '17 at 10:27
  • I tried this query in an SQLite DB browser and it works there. `UPDATE State_Regions SET Show_Ad = 1 WHERE Country_ID IN ( 2 ) AND State_ID IN ( 12 ) AND Region_ID IN (15, 14)` Should execute a `rawQuery` in my app? – Umar Ali Dec 20 '17 at 10:33
  • Indeed, something like `IN (15, 14)` will work fine -- but if you want to parameterize that query, you'd need it to be `IN (?, ?)`, not `IN (?)`. `rawQuery` is fine, but won't help with the parameterization. – Snild Dolkow Dec 20 '17 at 12:10