MS Access UPDATE Query errors

Question

I am having a hard time using MS Access because the syntax is a little finicky compared to other db's.

I am trying to validate a table and compare that table to a master table with multiple columns of information. At the moment I am trying to update a table with a field name of Difference_Value in table ct2011 to be equal to (ct2011.Distribution_Amount - AggregateFinal.SumOfDollars).

Also specifying the lines in which are going to be updated because not all rows in the MASTER are in the table ct2011.

Below is my query.

UPDATE ct2011
SET ct2011.Difference_Value = (ct2011.Distribution_Amount - AggregateFinal.SumOfDollars)
FROM
       ct2011 as ct
INNER JOIN 
       AggregateFinal af
ON
       ct.Employee_ID = af.EmpId AND ct.Legal_Name = af.LegalName AND ct.Distribution_Plan_Year = af.CalculationAwardPeriod AND ct.Award_Year = af.AwardPeriod;

I am getting a Syntax error (missing operator). It specifies that it is encountering the error during the SET expressions after the =.

And what exactly is not working with the given query? Is there any error message? — Nico Haase, Feb 05 '19 at 15:24
@NicoHaase I am getting a Syntax error (missing operator). It specifies that it is encountering the error during the SET expressions after the =. — A.Bell, Feb 05 '19 at 15:31
You set a table alias of `ct` for ct2011, but kept using `ct2011` to fully qualify the field names. — HardCode, Feb 05 '19 at 19:54
There is no `FROM` clause in `UPDATE` queries in MS Access. And before you claim that is *finicky*, MySQL also adheres to this same [`UPDATE ... JOIN`](https://stackoverflow.com/q/2114534/1422451) style. Still *finicky*? Try writing a join in [`UPDATE` query in Oracle](https://stackoverflow.com/q/2446764/1422451) which neither supports `JOIN` or `FROM` unless inside a subquery, similar also to [SQLite](https://stackoverflow.com/q/19270259/1422451). Yes, all SQL dialects are *finicky*! — Parfait, Feb 05 '19 at 20:23

score 1 · Answer 1 · answered Feb 05 '19 at 18:07

In an MS Access update query, the join criteria should follow the update keyword, e.g.:

update 
    ct2011 ct inner join aggregatefinal af on
    ct.employee_id = af.empid and 
    ct.legal_name = af.legalname and 
    ct.distribution_plan_year = af.calculationawardperiod and
    ct.award_year = af.awardperiod
set 
    ct.difference_value = (ct.distribution_amount - af.sumofdollars)

MS Access UPDATE Query errors

1 Answers1