CODEWITHSUNDEEP
javaandroidsqlsqlite
Addev
Addev

Reputation: 32233

too many SQL variables and NOT IN clause

I have a table with a collection of ids (sqlite Android), and a collection of ids I want to retain.

Then if toRetain = [1,2,3,4] I create an execute:

DELETE FROM my_items WHERE _id NOT IN (1,2,3,4);

But if the length of the items to retain is very high the SQL engine throws an exception: "too many SQL variables". Since the clause is NOT IN I can't do it in smaller steps.

How can i solve it?

One solution I think is to insert all the ids in a temporal table and then execute:

DELETE FROM my_items WHERE _id NOT IN (SELECT_id FROM items_to_delete);

Is that solution correct and efficient??

Upvotes: 4

Views: 2981

Answers (2)

Adam Monos
Adam Monos

Reputation: 4297

Android doesnt like it when you hardcode the values in the query, try 

db.delete("my_items", "_id NOT IN (?, ?, ?, ?)", String[] {"1", "2", "3", "4"});

Or if you use ContentProvider, then:

getContentResolver().delete("content://uri/to/my/items", "_id NOT IN (?, ?, ?, ?)", String[] {"1", "2", "3", "4"})

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13496

I think creating a temp table or table variable and then insert all the ids to be retained in that. Then delete from the main table by joining main table with this temp or table variables.

Here I suggest to create an index if you use temp table also use the inner join insted of NOT IN

Upvotes: 5

Related Questions