Error on SQL query on Access Database

Question

Here is the problem:

UPDATE school_new 
  SET school_new.school_id = school.[School ID], 
      school_new.school_address = school.[School Address]
FROM school_new
INNER JOIN school on school_new.school_name = school.[School Name]

And I get this error and I can't figure out what is the problem

Syntax error (missing operator) in query expression 'school.[School ID] FROM school_new INNER JOIN school on school_new.school_name = school.[School Name]

Answer 1

There is a slight change in the update query with join on access

UPDATE school_new 
INNER JOIN school on school_new.school_name = school.[School Name]
  SET school_new.school_id = school.[School ID], 
      school_new.school_id = school.[School ID]

Answer 2

List the tables and join condition before the SET keyword.

UPDATE school_new
INNER JOIN school
    ON school_new.school_name = school.[School Name]
SET school_new.school_id = school.[School ID]

In your example, you had school_new.school_id = school.[School ID] assigned twice; I did that SET only once. This point is important because if you list it twice, the db engine will throw an error...

Duplicate output destination 'school_new.school_id'.

Based on the update to your question ...

UPDATE school_new
INNER JOIN school
    ON school_new.school_name = school.[School Name]
SET school_new.school_id = school.[School ID], 
    school_new.school_address = school.[School Address]

Answer 3

I know this would sound strange but try to create a sample table e.g school_new_Test and in that do not give name with spaces and same way create duplicate table of school e.g school_ORG_Test in that also do not give spaces and then try the above query with new tables and collumn names without spaces.

Answer 4

Generally, 'update' statements can't have a join. Use a nested query thus

update school_new
set school_new.school_id = (select school.[school ID]
from school where school.[school name] = school_new.school_name)