SQL Update on joined tables with calculated fields

Question

First of all, I know there are already questions and answers about it, this thread being the one that is closest to what I need:

SQL Update to the SUM of its joined values

However, I get a syntax error (operator missing) that seems to occur close to the FROM clause. However I can't see it. Does it not like the FROM itself ? I am not used to using FROM in an update statement but it seems like it's valid from the QA I just linked :|

Any idea why there would be a syntax error there ?

I am using Access 2007 SP3.

Edit: Wow, I forgot to post the query...

UPDATE r

SET
    r.tempsmoy_requete_min = tmm.moy_mob_requete

FROM
    rapports AS r INNER JOIN
    (SELECT 
        id_fichier, 
        Round(Sum(temps_requete_min)/3,0) As moy_mob_requete,
        Round(Sum(temps_analyse_min)/3,0) As moy_mob_analyse, 
        Round(Sum(temps_maj_min)/3,0) As moy_mob_maj, 
        Round(Sum(temps_rap_min)/3,0) As moy_mob_rap, 
        Round(Sum(temps_ddc_min)/3,0) As moy_mob_ddc

    FROM maintenances 

    WHERE 
        periode In (10,9,8) And 
        annee=2011

    GROUP BY id_fichier) AS tmm ON rapports.id_rapport = tmm.id_fichier

WHERE 
    1=0

The WHERE 1=0 part is because I want to test further the subquery before running it.

Edit: This is some simpler query I am trying. I get a different error this time. It now tells me that tempsmoy_requete_min (and probably all other left operands) are not part of an aggregate function... which is the point of my query. Any idea ?

UPDATE 
    rapports INNER JOIN maintenances ON rapports.id_rapport = maintenances.id_fichier

SET
    rapports.tempsmoy_requete_min = Round(Sum(temps_requete_min)/3,0),
    rapports.tempsmoy_analyse_min = Round(Sum(temps_analyse_min)/3,0),
    rapports.tempsmoy_maj_min = Round(Sum(temps_maj_min)/3,0),
    rapports.tempsmoy_rap_min = Round(Sum(temps_rap_min)/3,0),
    rapports.tempsmoy_ddc_min = Round(Sum(temps_ddc_min)/3,0)

WHERE 
    maintenances.periode In (10,9,8) And 
    maintenances.annee=2011 AND
    1=0

enter image description here

That was what I had at first (edited my code) and I got the same error. That's why I messed around with the code to find the right syntax. — ApplePie, Sep 07 '12 at 13:38
Done. Sorry I don't have just an SQL query for that. I did it manually in the visual editor. — ApplePie, Sep 07 '12 at 13:57

score 0 · Answer 1 · edited May 25 '22 at 19:26

0

UPDATE r

should be

UPDATE rapports

You can't reliably use an alias in the update target.

edited May 25 '22 at 19:26

halfer

19,824
17
99
186

answered Sep 07 '12 at 13:36

StingyJack

19,041
10
63
122

score 0 · Accepted Answer · answered Sep 07 '12 at 16:00

0

I tried adapting your first query sample, and was able to make your error go away. However then I encountered a different error ('Operation must use an updateable query').

It may be possible to overcome that error, too. However, I found it easier to use a domain function instead of a join to retrieve the replacement value.

UPDATE rapports
SET tempsmoy_requete_min = Round(DSum("temps_requete_min",
    "maintenances",
    "periode In (10,9,8) AND annee=2011 "
    & "AND id_fichier='" & id_rapport
    & "'")/3, 0);

If this suggestion works for tempsmoy_requete_min with your data, you will have to extend it to the other fields you want to replace. That won't be pretty. You could make it less ugly with a saved query which you then use as the "Domain" parameter for DSum() ... that could allow you to use a simpler "Criteria" parameter.

answered Sep 07 '12 at 16:00

HansUp

95,961
11
77
135

I'll trust you have a compelling reason to store those derived values rather than just deriving them when needed. Personally, my impulse would be to avoid doing this. But I don't know anything about the details of your situation. – HansUp Sep 07 '12 at 16:06
I will try this when I get the chance to. Also you're right maybe I should rethink my process as I was just continuing what was already being done in the original database (had to normalize it...). Basically it's used as a reference point in a KPI database. I want to set this reference point to the moving average of the past 3 months (for the moment hardcoded). However, maybe I could stop dealing with those intermediate variables in `rapports` and directly put the computed values in `maintenances` when I create the KPIs for the current month. – ApplePie Sep 07 '12 at 17:36
I've given you the check mark for your suggestion *not* to do this. This is what will solve my problem in the end. Thanks and sorry for being almost a month late. – ApplePie Sep 30 '12 at 20:47

SQL Update on joined tables with calculated fields

2 Answers2