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.

Probably you are running your app on a device with a version of SQLite older than 3.24.0 when UPSERT was introduced. — forpas, Jul 18 '22 at 20:47

score 0 · Answer 1 · answered Jul 19 '22 at 06:00

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.

score 0 · Accepted Answer · answered Jul 20 '22 at 23:47

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.

This is not equivalent to UPSERT. Read this: https://stackoverflow.com/a/4253806/10498828 — forpas, Jul 21 '22 at 08:24

Upsert query in Dao

2 Answers2