none
Update query with Inner join RRS feed

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

    Thursday, May 11, 2017 7:57 PM

All replies

  • 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

    Thursday, May 11, 2017 9:20 PM
  • 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...


    Thursday, May 11, 2017 9:22 PM
  • 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.

    Thursday, May 11, 2017 9:25 PM