使用Inner join更新查询 [英] Update query with Inner join

问题描述

我尝试用另一个表中的另一个字段更新字段。我尝试这个代码和其他几个没有成功，我总是有一个语法错误。有人可以帮我吗？

Hi, i try to update a field with a another field from another table. I try this code and few others without success i always have a syntax error. Can someone please help me with that?

更新t1

设置t1.sous_total = t2.totalsum

来自estimé作为t1

内部联接（选择id_estimé，总和（总计）作为总数）
来自lignes_estimé

group by id_estimé）t2

在t2.id_estimé=t1.id_estimé

update t1
set t1.sous_total = t2.totalsum
from estimé as t1
inner join (select id_estimé , sum(total) as totalsum
from lignes_estimé
group by id_estimé) as t2
on t2.id_estimé = t1.id_estimé

推荐答案

Access（Jet）SQL语法更像是这样：

Access (Jet) SQL syntax would be more like this:

   update
        estimé as t1
    inner join
        (select id_estimé, sum(total) as totalsum
         from lignes_estimé group by id_estimé) as t2
    on t2.id_estimé = t1.id_estimé
    set t1.sous_total = t2.totalsum

但是，这仍然无效，因为Access会向您显示错误消息"操作必须使用可更新的查询"。 那是因为它会在派生表t2的定义中看到GROUP BY子句，并得出结论整个查询
是不可更新的。

HOWEVER, that still won't work, because Access will give you the error message, "Operation must use an updatable query".  That's because it will see the GROUP BY clause in the definition of derived table t2 and conclude that the whole query is non-updatable.

你可以解决这个问题，效率低下，通过使用DSum（）函数，沿着以下几行：

You can work around this, inefficiently, by using the DSum() function, along these lines:

UPDATE estimé SET sous_total = DSum("total", "lignes_estimé", "id_estimé=" & id_estimé)

这篇关于使用Inner join更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！