1

I'm trying to update a microsoft access database table field from a similar field in a linked table.

Here are my table names:

Raw data
sectionroster

And here's my query so far:

UPDATE [raw data].[current supervisor]
FROM [raw data] 
INNER JOIN [sectionroster] ON [raw data].[associate id]=[sectionroster].[employee number]
SET [raw data].[Current Supervisor] = [sectionroster].[supervisor];

It's giving me a syntax error referencing the from clause, and I can't figure out why. Any help would be appreciated!

Nathan Hughes
  • 94,330
  • 19
  • 181
  • 276
user965323
  • 11
  • 1

1 Answers1

0

Try this

UPDATE [raw data].[current supervisor]
SET [raw data].[Current Supervisor] = [sectionroster].[supervisor]
FROM [raw data] 
INNER JOIN [sectionroster] ON [raw data].[associate id]=[sectionroster].[employee number]
Vinay
  • 1,016
  • 8
  • 22
  • No problem. Did it work? If so, please accept the answer so that it helps the others. – Vinay Sep 26 '11 at 15:40
  • Thanks for the fast response! However, its giving me the exact same error! Is it possible that I some how have the relationship wrong between the 2 tables? I have [raw data].[associate id] directly linked to [sectionroster].[employee number]. and this is the only relationship existing on either table. Thanks again! JD – user965323 Sep 26 '11 at 15:41
  • The query looks about right. I found another question on stack overflow. May be this could help you solve the problem. http://stackoverflow.com/questions/537161/sql-update-woes-in-ms-access-operation-must-use-an-updateable-query – Vinay Sep 26 '11 at 15:58
  • Thanks again, Vinay! I just figured it out! – user965323 Sep 26 '11 at 16:00
  • This is what worked for me! UPDATE [raw data] inner join [sectionroster] ON [raw data].[associate id]=[sectionroster].[employee number] SET [raw data].[Current Supervisor] = [sectionroster].[supervisor] ; – user965323 Sep 26 '11 at 16:01
  • Oh damn! Yeah, I missed that, You have to update a table always and not a particular row. Sorry about that. – Vinay Sep 26 '11 at 16:03
  • This is yet another case that if the QBE were used to generate the SQL, this kind of mistake wouldn't have happened in the first place. – David-W-Fenton Sep 26 '11 at 20:40