我想将table 1中的某些字段更新为与另一个table(甚至是子查询)中的值相同。问题是,当有多个字段时,我在compilation error更新查询中得到了DAO。您可以在下面看到代码(改写为通用代码):@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();我用SQLite Update Query Ref中的注释检查了查询,并尝试在DB Browser中的SQLite代码中正常工作。任何帮助将不胜感激。 最佳答案 您在第4行上忘记了+。如果这仅是复制/粘贴错误,则在您尝试使该问题通用时,您删除的原始查询中肯定有问题。这应该工作,这是一个工作示例:Online example代码: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 table1SET (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;因此,也许可以尝试构建一个更接近原始查询的在线最小示例。编辑:根据您的评论,我已经测试过,可以在我的项目中重现该问题,确实它不能编译,错误是:  错误:输入“更新测试集(名称,  多余的输入')',期望{,';',',','=','*','+','-',  '||','/','%','','&','|','','> =',' ==',  '!=','',...}不幸的是,我认为这是一个房间限制。也许您可以将问题提交给Google,以获得有关此问题的更多信息。同时,您可以尝试使用RawQuery(如文档中所述,它必须返回非空类型,但是您可以返回例如假字符串或空POJO):@RawQueryString setFieldsInTable1(SupportSQLiteQuery query);然后像这样使用它:SimpleSQLiteQuery query = new SimpleSQLiteQuery("UPDATE table1 SET ...");yourDao.setFieldsInTable1(query);另一个解决方案是通过这种方式处理两个子查询:UPDATE table1SET 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);同样根据您的应用程序工作流程,您可以在代码中修改实体,然后使用Update界面进行更新:@Updatevoid update(Table1Entity... table1Entities);
09-10 06:20
查看更多