Room delete multiple rows by WHERE IN clause not working

Question

I want to delete multiple rows by their IDs in Android Room by DELETE FROM...WHERE...IN (...) clause. Below is the code:

ItemDao

@Dao
interface ItemDao {
    @Query("DELETE FROM Item WHERE id IN (:ids)")
    fun deleteItemByIds(ids: String)
}

ItemViewModel

class ItemViewModel(application: Application) : AndroidViewModel(application) {
    fun deleteByIds(ids: String) {
        mScope.launch(Dispatchers.IO) {
            mItemDao.deleteItemByIds(ids)
        }
    }
}

ItemActivity

fun onDelete(){
    // these are not real IDs, just for demo
    val itemIdList = arrayListOf<Long>(1, 2, 3)
    val ids = itemIdList.toString().drop(1).dropLast(1) // ids = "1, 2, 3"
    itemViewModel.deleteByIds(ids)
}

When there's only one ID, the code works. But when there are multiple IDs, it doesn't. So what happened here? Thanks for your help.

Answer 1

You need to pass an array or list when you are dealing with WHERE IN clause. Like:

@Dao
interface ItemDao {
    @Query("DELETE FROM Item WHERE id IN (:ids)")
    fun deleteItemByIds(ids: Array<Long>)
}

Answer 2

I know the question asks for deleting the rows with a list of Ids but my app crashes when I'm passing 10k+ Ids. So instead I tried passing the object itself and it worked for me.

@Dao
interface ItemDao {

    @Delete
    fun deleteUserByList(userList: Array<User>)

}

This way I'm able to delete more than 10k rows.

In case anyone needs it.