How do I update columns of a Room entity only if it's null?

Question

Is there a way to update columns of an entity only if the columns are null? Here's my Update function in my DAO interface:

@Query("UPDATE media SET media_name = :mediaName, media_data = :mediaData WHERE id = :id")
fun update(id: Int, mediaName: String?, mediaData: String?)

... because as of now, a value of "null" is updated for these columns.

Do you mean `UPDATE media SET media_name = :mediaName, media_data = :mediaData WHERE id = :id AND media_name IS NULL AND media_data IS NULL`? — CommonsWare, Mar 14 '18 at 17:58
@CommonsWare Sorry for not being clear, but pretty much with non-null params only being updated to the database, so like `@Query("UPDATE media SET media_name = :mediaName, media_data = :mediaData WHERE id = :id AND :mediaName IS NOT NULL AND :mediaData IS NOT NULL")` except that this doesn't work — DaveNOTDavid, Mar 14 '18 at 23:22
Um, just check for `null` on those values before calling `update()`. — CommonsWare, Mar 14 '18 at 23:29
@CommonsWare Yeah, that's what I thought, but was wondering if there was a more ideal SQL alternative for this. Thanks Mark :-) — DaveNOTDavid, Mar 15 '18 at 00:41

score 3 · Answer 1 · edited Jun 06 '18 at 16:18

3

You can try:

@Query("UPDATE media SET media_name = (CASE WHEN media_name IS NULL THEN :mediaName ELSE media_name END), media_data = (CASE WHEN media_data IS NULL THEN :mediaData ELSE media_data END) WHERE id = :id")
fun update(id: Int, mediaName: String?, mediaData: String?)

edited Jun 06 '18 at 16:18

Zoe

27,060
21
118
148

answered Jun 06 '18 at 15:31

Alexis GENET

31
3

How do I update columns of a Room entity only if it's null?

1 Answers1