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]

No inner join for UPDATE queries

KazuNino
– KazuNino

2012-07-26 05:13:03 +00:00
Commented Jul 26, 2012 at 5:13 — KazuNino
– KazuNino, Commented Jul 26, 2012 at 5:13
Why are you updating the same value twice?

No'am Newman
– No'am Newman

2012-07-26 05:23:20 +00:00
Commented Jul 26, 2012 at 5:23 — No'am Newman
– No'am Newman, Commented Jul 26, 2012 at 5:23

ZafarYousafi · Accepted Answer · 2012-07-26 05:15:34Z

4

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]

answered Jul 26, 2012 at 5:15

ZafarYousafi

11.1k5 gold badges35 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HansUp · Accepted Answer · 2012-07-26 06:00:09Z

1

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]

edited Jul 26, 2012 at 6:00

answered Jul 26, 2012 at 5:19

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

1 Comment

Ibu Over a year ago

Thank you, your answer works. I made a typo on my question. but using the join first solves the problem

Jigar Pandya · Accepted Answer · 2012-07-26 05:11:02Z

0

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.

answered Jul 26, 2012 at 5:11

Jigar Pandya

5,9852 gold badges29 silver badges47 bronze badges

Comments

No'am Newman · Accepted Answer · 2012-07-26 05:10:06Z

-3

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)

answered Jul 26, 2012 at 5:10

No'am Newman

6,4975 gold badges42 silver badges54 bronze badges

2 Comments

No'am Newman Over a year ago

@ZafarYousafi: there can be a join in an update statement if the table to be updated is aliased (see stackoverflow.com/questions/1293330/sql-update-with-join). There is, however, no aliasing in the above question.

Christian Specht Over a year ago

That's not true. You can use updates with joins no matter of the table is aliased or not. Plus, this question is about MS Access SQL, and the question you linked is not.

Collectives™ on Stack Overflow

Error on SQL query on Access Database

4 Answers 4

Comments

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related