androidsqlsqliteandroid-roomandroid-components

SQL delete where NOT IN doesn't work

I am using room dao from Android Components. What i need: insert list, but before - delete all Data from db whose id is different from id from the new list.

here is my simple Data class:

class Data{
   String id; //unique for each data class

   //some other data parameters here
}

The best option for my task is use DELETE WHERE NOT IN someArray. here is example from my code:

@Query("DELETE " +
        "FROM MY_TABLE " +
        "WHERE data_id NOT IN ( :listOfId )")
abstract protected fun deleteUniqueData(listOfId:String):Int

So when I receive new list that must be saved, I create next String with ids: "'0','1'" and call method deleteUniqueData.

But each time I called this, I receive 2 as result, which means that there 2 rows was deleted.

Interesting part: when I replace

"WHERE data_id NOT IN ( :listOfId )")

with

"WHERE data_id NOT IN ('0','1')") //same data but hardcoded

I receive 0 as result. So it looks like it is some problem with Room.

Any ideas why NOT IN doesn't work? Or how it can be fixed?

UPDATE

here is generated query:

"DELETE FROM MY_TABLE WHERE data_id NOT IN ( ? )"
Solution

  • Change deleteUniqueData() to take a list or array of IDs as the parameter. Do not assemble the comma-delimited list yourself. Room does that.