CODEWITHSUNDEEP
sqlms-access
Ibu
Ibu

Reputation: 43850

Error on SQL query on Access Database

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]

Upvotes: 0

Views: 136

Answers (4)

HansUp
HansUp

Reputation: 97131

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]

Upvotes: 1

ZafarYousafi
ZafarYousafi

Reputation: 10920

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]

Upvotes: 4

Jigar Pandya
Jigar Pandya

Reputation: 5987

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.

Upvotes: 0

No'am Newman
No'am Newman

Reputation: 6477

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)

Upvotes: -3

Related Questions