Android Room @Delete with parameters

Question

I know I can't use DELETE in a query (that is a shame by the way), I will get the following error:

<i>Error:error: Observable query return type (LiveData, Flowable etc) can only be used with SELECT queries that directly or indirectly (via @Relation, for example) access at least one table.</i>

But I can't use @Delete(WHERE... xxx) So how do I delete a specific row by a parameter?

score 343 · Answer 1 · edited Dec 29 '18 at 11:22

343

Actually, you can use @Query to perform a delete.

@Query("DELETE FROM users WHERE user_id = :userId")
abstract void deleteByUserId(long userId);

Extracted from Query javadoc:

UPDATE or DELETE queries can return void or int. If it is an int, the value is the number of rows affected by this query.

edited Dec 29 '18 at 11:22

frogatto

28,539
11
83
129

answered Nov 29 '17 at 14:03

Maragues

37,861
14
95
96

2

this works, I've been using something like this and *delete all* is written this way too. for reference check this: (https://stackoverflow.com/a/44249498/2185548) – Gama the Great Apr 10 '18 at 06:36
1

@Delete doesn't work for me for some reason. But this works! – Rakesh May 31 '18 at 11:47
2

@Rakesh You might be having an autogenerated primary id in your entity, which means you can only delete is using some parameter, like "userId" here. – Ajith M A Oct 16 '18 at 09:25
Two questions, since I have an auto generated primary id dos that mean even for my update methods I have to use an id too, and what's the difference between `delete`(normal) and `delete` with `id` queries? – George Udosen Feb 14 '19 at 15:08
1

Thanks for the information @Maragues. the "UPDATE or DELETE queries can return void or int. If it is an int, the value is the number of rows affected by this query." stuff is what I was looking for. it worked for me. – Fernando Perez Aug 26 '21 at 13:05

score 91 · Accepted Answer · answered Mar 29 '18 at 07:40

91

The beauty of room is, we play with the objects. As per requirement you can use for kotlin:

@Delete
fun delete(model: LanguageModel)

for Java:

@Delete
void delete(LanguageModel model)

it will delete the exact object which is stored in the db with the same values. LanguageModel is my model class and it works perfectly.

answered Mar 29 '18 at 07:40

Awais

1,157
9
15

20

Just a minor observation: [The documentation](https://developer.android.com/training/data-storage/room/accessing-data#convenience-delete) only mentions matching on primary keys for `@Delete` annotated methods. It is a bit unclear what will happen if there is no primary key definition in the model. – dbm May 27 '18 at 18:15
3

If it were to "delete the exact object which is stored in the db with the same values", it would be useless, because objects change in the DB, and deleting them later on shouldn't require me to know their exact data; I only need to know the primary key. – Alaa M. Jul 24 '20 at 08:58
2

@dbm atleast one primary key is required in an Entity in roomdb – AndroidLearner Jan 19 '21 at 12:38
@AndroidLearner that's actually a valid point. Shortcut to documentation for future visitors: https://developer.android.com/training/data-storage/room/defining-data – dbm Jan 21 '21 at 10:14
1

That's not the beauty, that's a hassle if you know the id to delete but don't want to fetch the whole object – zaitsman Apr 06 '22 at 06:37

score 45 · Answer 3 · edited Aug 04 '21 at 18:18

45

You can use below method to delete by ID

@Query("DELETE FROM yourDatabaseTable WHERE id = :id")
void deleteById(int id);

for delete all rows

@Query("DELETE FROM yourDatabaseTable")
void delete();

edited Aug 04 '21 at 18:18

Ehsan Shadi

609
6
17

answered May 25 '20 at 07:44

Fakhriddin Abdullaev

4,169
2
35
37

Hi! Using second method to delete the database, is it possible to return a value (true or false) whether the deletion is successfully done or note? Thanks! – Mark Delphi Aug 28 '21 at 21:57
4

@MarkDelphi If `delete` operation was successful, it would return number of rows that have been deleted. So a return of `-1` implies operation failed. – Sourav Kannantha B Nov 09 '21 at 17:05

score 6 · Answer 4 · answered Jan 13 '22 at 14:27

6

ROOM database provides easy way to INSERT, UPDATE and DELETE an object in the database. To perform thus operation just needed to annotate @Delete. The DELETE operation returns the Int when deletion of the single object is successful returns 1 else returns 0 if the DELETE operation is unsuccessful, Adding the return type is a good practice.

KotlinEG.kt

   @Dao
   interface EntityLocalDAO {
       @Delete
       fun deleteData(entityObject: EntityObject) : Int
   }

javaEG.java

   @Dao
   interface EntityLocalDAO {
       @Delete
       int deleteData(EntityObject entityObject);
   }

answered Jan 13 '22 at 14:27

Rohit S

714
5
7

Does Insert and Update returns int as well? – Bitwise DEVS Apr 04 '22 at 12:53
Insert return Long value :) – KamDroid Jul 03 '22 at 03:57
Insert return is the inserted row's id – Erdal G. Apr 23 '23 at 07:47

score 3 · Answer 5 · answered May 24 '22 at 01:33

You can now delete using only partial data.

Per the documentation:

@Entity
data class Playlist (
    @PrimaryKey
    val playlistId: Long,
    val ownerId: Long,
    val name: String,
    @ColumnInfo(defaultValue = "normal")
    val category: String
)

data class OwnerIdAndCategory (
    val ownerId: Long,
    val category: String
)

@Dao
public interface PlaylistDao {
    @Delete(entity = Playlist::class)
    fun deleteByOwnerIdAndCategory(varargs idCategory: OwnerIdAndCategory)
}

In this example you can see that they are deleting the Playlist using only the ownerId and the category. You do not even need to use the primary key (playlistId).

The key is to use the @Delete(entity = Playlist::class) annotation.

Android Room @Delete with parameters

5 Answers5

Linked