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.

score 25 · Accepted Answer · edited Mar 26 '19 at 08:24

25

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>)
}

edited Mar 26 '19 at 08:24

Twisted Lullaby

551
1
8
20

answered Mar 25 '19 at 15:36

Sdghasemi

5,370
1
34
42

I changed the type to `Array` since the id type is `Long` and it works. Thanks a lot! – Twisted Lullaby Mar 25 '19 at 17:18
@TwistedLullaby you're welcome. Feel free to update the answer with your working code in case someone needs it. – Sdghasemi Mar 25 '19 at 19:58
Can you tell me what could be the maximum length of that list or where can I find out about this, any docs mentioning the limits? I'm getting an error. – Vijay Jan 18 '21 at 11:50
@Vijay The limit is hardcoded in `sqlite.c` file, learn more [here](https://stackoverflow.com/a/15313495/4399414). What you should do is to split your array or list into smaller chunks below the limit and execute the query multiple times to achieve a complete deletion. – Sdghasemi Jan 18 '21 at 14:57
Instead of passing the ArrayList of ids now I passing ArrayList of the object itself and then annotating it with simple @Delete. This worked for me even for more than 10k deletion. Thanks. – Vijay Jan 19 '21 at 06:50

score 2 · Answer 2 · answered Jan 21 '21 at 06:19

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.

answered Jan 21 '21 at 06:19

Vijay

376
4
13

Basically, I agree with you. But the concepts are the same. – A S M Sayem Apr 27 '21 at 09:13

Сергей Зайцев · Answer 3 · 2022-05-19T08:43:30.877

I had the a similar problem with RoomDatabase delete qwery. The problem was solved by apply of array type as a @Query function argument, but no list or string.

IT IS NOT WORKING:

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

OR:

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

IT IS WORKING:

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

Room delete multiple rows by WHERE IN clause not working

3 Answers3