Update query with Inner join

Question

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?

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é

Answer 1

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

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.

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é)

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

Hi,

I think because your subquery is a Totals query, your resulting recordset is not updateable. You could try the following instead:

UPDATE estime T1
SET T1.sous_total=DSum("total","estime","id_estime=" & T1.id_estime)

(untested)

Hope it helps...

Edit: Sorry for the duplicate info...

Answer 3

ciao YukSeb,

some other workAround further than what Dirk explained here :

https://expertreplies.com/acc-update-query-based-on-totals-query-fails/

HTH.

ciao!

Sandro.