Android Room Database what is the correct syntax to perform bulk update?

Question

I was working on a project, where There is a table named Messages(uuid, body, metadata_list, ... ...), and I want to perform bulk update on message_body, and metadata_list columns. I followed this answer.

I created a data class

MyTuple(    
    @ColumnInfo(name = "uuid") var uuid: String,
    @ColumnInfo(name = "body") var body: SpannableStringBuilder,
    @ColumnInfo(name = "mention_metadata_list") var mentionMetaDataList: List<MentionsMetaData>? = ArrayList<MentionsMetaData>()
)

Then I queried these column and got a list: List<MyTuple>, modified it, and now I want to bulk update these new values inside the sqlite database.

I got stuck, so created a SimpleSQLiteQuery object like this:

String rawSqlInput = "(1, 'foo1', ''bar1), (2, 'foo2', 'bar2')"; // generated using for loop.

String rawQuery = "WITH temp_table(id, new_body, mmd_list)  AS  (VALUES" + rawSqlInput + ")  UPDATE messages SET body = (SELECT new_body FROM temp_table WHERE temp_table.id == messages.uuid), mention_metadata_list = (SELECT mmd_list FROM temp_table WHERE temp_table.id == messages.uuid) WHERE uuid IN (SELECT id FROM TEMP_TABLE)";

SimpleSQLiteQuery simpleSQLiteQuery = new SimpleSQLiteQuery(rawQuery);
mChatDao.bulkUpdateBodyAndMetaDataForGroupMessages(simpleSQLiteQuery);  // this one works fine

And my dao is like this:

@Dao
abstract class ChatDao {
    @RawQuery
    abstract fun bulkUpdateBodyAndMetaDataForGroupMessages(query: SimpleSQLiteQuery): Int;
}

This works ok.

The problem

I don't want to create rawSqlInput variable, and use simpleSQLiteQuery above. Instead I want to pass the list: List<MyTuple> inside the dao method, and let room handle everything for me.

I tried this but it failed:

@Dao
abstract class ChatDao {
@Query("WITH temp_table(id, new_body, mmd_list)  AS (VALUES (:mmdTuples))  UPDATE messages SET body = (SELECT new_body FROM temp_table WHERE temp_table.id == messages.uuid), mention_metadata_list = (SELECT mmd_list FROM temp_table WHERE temp_table.id == messages.uuid) WHERE uuid IN (SELECT id FROM TEMP_TABLE)")
abstract fun bulkUpdateBodyAndMetaDataForGroupMessages(mmdTuples: List<MyTuple>)

}

But it did not work. I get error at the part "...AS (VALUES(:mmdTuples)) ... ". So what is the correct syntax of doing bulk update using room?

Answer 1

@Update
abstract fun update(myTuple : MyTuple)

//This function is under a transaction so it will be commited to the database
//all at the same time.
@Transaction
fun update(list : List<MyTuple>){
   list.forEach{
      update(it)
   }
}

This is just one way to handle it but you could go the upsert approach that will most likely serve you better in the long run. Android Room Persistence Library: Upsert