Upsert query in Dao-CodePudding

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.

CodePudding user response：

For versions of SQLite that do not support UPSERT you need 2 separate statements.

First, try to update the table:

UPDATE letter_table SET letterScore = letterScore   1 WHERE letter = :letter

If :letter does not exist in the table then nothing will happen.

Then, try to insert a new row:

INSERT OR IGNORE INTO letter_table(letter, letterScore) VALUES (:letter, 0)

If :letter already exists in the table then nothing will happen.

CodePudding user response：

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.