Room Update Multiple Fields

Question

I would like to update some fields from table 1 to be the same as the values from another table (or even a subquery). The issue is I get a compilation error in my DAO update query when there is more than 1 field.

You can see the code (rewritten to be generic) below:

@Query("UPDATE table1 " +
     " SET (field1, field2) = " +
     " (SELECT SUM(table2.foreign_field1) as summaryForeign_field1, table2.foreign_field2 " +
     " FROM table2 " +
     " WHERE   table1.id = table2.id " + 
     " GROUP BY table2.foreign_field2 ) ")
void setFieldsInTable1();

I checked the query with the notes in SQLite Update Query Ref and I have tried the code in DB Browser for SQLite where it works fine.

Any help would be greatly appreciated.

Answer 1

You forgot a + on line 4.

If this is just a copy/past error then there must be something wrong in your original query that you removed when you tried to make it generic for your question.

This should work, here is a working example :

Online example

Code :

BEGIN TRANSACTION;

/* Create tables */
CREATE TABLE table1(
    id integer PRIMARY KEY,
    field1 text,
    field2 text
);

CREATE TABLE table2(
    id integer PRIMARY KEY,
    field1 text,
    foreign_field1 integer,
    FOREIGN KEY(foreign_field1) REFERENCES table1(id)
);

/* Create few records */
INSERT INTO table1 VALUES(1,'Tom', 'French');
INSERT INTO table1 VALUES(2,'Lucy', 'American');
INSERT INTO table1 VALUES(3,'Frank', 'English');
INSERT INTO table1 VALUES(4,'Jane', 'Polish');
INSERT INTO table1 VALUES(5,'Robert', 'French');

INSERT INTO table2 VALUES(1,'Monday', 3);
INSERT INTO table2 VALUES(2,'Wednesday', 5);
INSERT INTO table2 VALUES(3,'Friday', 1);
INSERT INTO table2 VALUES(4,'Tuesday', 4);
INSERT INTO table2 VALUES(5,'Monday', 1);

COMMIT;

/* Display records from the table1 */
SELECT * FROM table1;

/* Update records from table1 */
UPDATE table1 
SET (field1, field2) = (
    SELECT
        SUM(table2.foreign_field1) as summaryForeign_field1,
        table2.field1 
    FROM table2 
    WHERE table1.id = table2.id 
    GROUP BY table2.field1
);

/* Check that the update has been executed correctly */
SELECT * FROM table1;

So maybe try to build an online minimal example closer to your original query.

EDIT : OK as per your comment I have tested and can reproduce the problem in my project, indeed it does not compile, error is :

error: no viable alternative at input 'update test SET (name,' extraneous input ')' expecting {, ';', ',', '=', '*', '+', '-', '||', '/', '%', '<<', '>>', '&', '|', '<', '<=', '>', '>=', '==', '!=', '<>', ...}

Unfortunately I think this is a Room limitation. Maybe you can submit an issue to Google to get more info about this.

Meanwhile, you can try with RawQuery (as stated in the doc it MUST return a non-void type but you can return a fake string or empty POJO for example) :

@RawQuery
String setFieldsInTable1(SupportSQLiteQuery query);

Then use it like this :

SimpleSQLiteQuery query = new SimpleSQLiteQuery("UPDATE table1 SET ...");
yourDao.setFieldsInTable1(query);

Another solution is to just go with 2 subqueries this way :

UPDATE table1
SET field1 = (
    SELECT SUM(table2.foreign_field1)
    FROM table2
    WHERE table1.id = table2.id
    GROUP BY table2.foreign_field2
),
field2 = (
    SELECT table2.foreign_field2
    FROM table2
    WHERE table1.id = table2.id
);

Also depending on your application workflow, you can modify your entity in your code and then use the Update interface to update it:

@Update
void update(Table1Entity... table1Entities);