android Room, how to delete multiple rows in the table by id list

Question

Having a Android Room with three tables timestamp, index, details, and all three have

@PrimaryKey @ColumnInfo(name = "id") var id: Int = 0

having fun clearDataByID(idList: List<Int>) to clear data from all three tables by the id in the idList

Dao as:

@Dao
interface DataDAO {


@Transaction
fun clearDataByID(idList: List<Int>) {
    deleteDataInTimestamp(idList)
    deleteDataIndex(idList)
    deleteDataDetails(idList)
}

@Query("delete from timestamp where id in :idList")
fun deleteDataInTimestamp(idList: List<Int>)

@Query("delete from index where id in :idList")
fun deleteDataIndex(idList: List<Int>)

@Query("delete from details where id in :idList")
fun deleteDataDetails(idList: List<Int>)
}

but it gets compiler error (similar for all three)

error: no viable alternative at input 'delete from timestamp where id in :idList'
public abstract void deleteDataInTimestamp(@org.jetbrains.annotations.NotNull()

if delete by single id it worked.

How to delete by a list of ids?

@Query("delete from timestamp where id = :id")
fun deleteSingleTimestamp(id: Int)

Have you looked at [this answer](https://stackoverflow.com/a/48370198/10805404)? It explains how to do a `SELECT` with a list of ids. — Simon Doppler, Apr 02 '19 at 15:40
thanks @SimonDoppler for point it out! it is the `obj_id IN (:ids)` missing the bracket. — lannyf, Apr 02 '19 at 17:03

score 55 · Answer 1 · edited Aug 22 '19 at 10:04

55

Thanks Simon points to the similar question, it should be done like:

@Query("delete from timestamp where id in (:idList)")
fun deleteDataInTimestamp(idList: List<Int>)

edited Aug 22 '19 at 10:04

denvercoder9

2,979
3
28
41

answered Apr 02 '19 at 20:34

lannyf

9,865
12
70
152

is this doable with a String list? – tpbafk Apr 28 '19 at 16:03
The braces are off in your answer. But thanks it works. For strings too – Devenom Aug 15 '19 at 14:28
What could be the maximum idList Size? – Vijay Jan 18 '21 at 06:40
1

crashing for large data – Waqas Yousaf Oct 24 '22 at 06:40

score 2 · Accepted Answer · answered Sep 20 '22 at 03:46

2

just extending the answer given by @lannyf

@Query("delete from timestamp where id in (:idList)")
fun deleteDataInTimestamp(idList: List<Int>)

this could be written in this way also

@Query("delete from timestamp where id = :idList")
fun deleteDataInTimestamp(idList: List<Int>)

PS: edit queue was full

answered Sep 20 '22 at 03:46

Ankit Kumar Maurya

991
7
20

1

The first way works! However the second one doesn't work The error is `near ",": syntax error (code 1 SQLITE_ERROR[1]): , while compiling: DELETE FROM product WHERE id = ?,?` – radus14 Nov 23 '22 at 13:06

android Room, how to delete multiple rows in the table by id list

2 Answers2