Upsert query in Dao

Question

I am developing an application using Room. The operation is simple, if the table has the same letter, the letterscore will increase by 1, otherwise it will be added and letterscore will be 0. In my Dao, I wrote a query as below to upsert the data, but it gives

compound operator>, LIMIT, ORDER, comma or semicolon expected, got 'ON'

error in the "ON" part:

@Dao
interface LetterDao {

    @Query("INSERT INTO letter_table(letter, letterScore) VALUES (:letter, :letterScore) ON CONFLICT(letter) DO UPDATE SET letterScore = letterScore + 1")
    suspend fun insertLetter(letter: String, letterScore: Int)
}

How can I fix this error or how can I do the upsert operation using Room ? Thanks for any help.

Answer 1

I changed this part in my code:

@Query("INSERT INTO letter_table(letter, letterScore) VALUES (:letter, :letterScore) ON CONFLICT(letter) DO UPDATE SET letterScore = letterScore + 1")
        suspend fun insertLetter(letter: String, letterScore: Int)

with

@Query("INSERT OR REPLACE INTO letter_table(letter, letterScore) VALUES (:letter, COALESCE((SELECT letterScore + 1 FROM letter_table WHERE letter=:letter), 0))")
        suspend fun insertLetter(letter: String)

and it worked.