Room Database update query with multiple joins-CodePudding

@Query("UPDATE ingredient_table AS it"  
        "SET quantity_needed = it.quantity_needed   1 "  
        "FROM recipe_ingredient_join_table "  
        "JOIN ingredient_table "  
        "ON it.ingredient_name = recipe_ingredient_join_table.ingredient_name "  
        "JOIN recipe_table "  
        "ON recipe_ingredient_join_table.recipe_name = recipe_table.recipe_name "  
        "WHERE recipe_table.recipe_name = :name")

I used DB Browser for SQLite to create this query. This query executes without errors in the DB Browser, but it will not compile in my Dao.

Error message: " '.', INDEXED, NOT or SET expected, got 'AS' "

I tried removing the AS statement and the 'it' aliases and then got the following error:

Error message: " BETWEEN, IN, WHERE comma, or semicolon expected got 'FROM' "

I would like to know what the correct syntax is and why this query won't compile, yet it will execute on DB Browser.

Is there a better tool or resource that can be used to create Room queries? What are the syntax differences between Room and SQLite, and where can they be found?

This query follows the structure outlined on SQLite.org

This is my first post on stackoverflow, thank you for your help.

CodePudding user response：

UPDATE FROM was introduced in version 3.33.0 of SQLite as per SQLite UPDATE 2.2 - UPDATE FROM

According to https://developer.android.com/reference/android/database/sqlite/package-summary SQLite 3.33.0 is not yet supported by any Android versions; as such UPDATE FROM will result in syntax errors.

For Android you will currently need to perform the update via an alternative method.