Update syntax for Access

Question

I need to update a field (which is currently empty) based on a match with another table. This should be simple, but my syntax is wrong.

In SQLServer 2005, the syntax would be

UPDATE Facilities-NOID
SET Facilities-NOID.ID = Facilities-ID.ID
FROM Facilities-NOID, Facilities-ID
WHERE [Facilities-ID].[Structure ID] = [Facilities-NOID].[Structure ID];

Access doesn't like that FROM line, but if I leave it out, it wants each ID. This should be simple, but I'm not seeing it in the MS help on the Update syntax.

Answer 1

Remove Facilities-NOID from the FROM clause. It is redundant because you've already specified the table in the first line.

Edit: I just saw that you showed the Sql Server 2005 syntax. Why didn't you show the Access syntax?

Answer 2

Remove the FROM clause completely

UPDATE Facilities-NOID
SET Facilities-NOID.ID = Facilities-ID.ID
WHERE [Facilities-ID].[Structure ID] = [Facilities-NOID].[Structure ID];

(that's the way that the query builder ends up constructing them)

Answer 3

I think this is what you want:

UPDATE Facilities-NOID 
INNER JOIN Facilities-ID ON Facilities-NOID.[Structure ID] 
    = Facilities-ID.[Structure ID]
SET Facilities-NOID.ID= Facilities-ID.ID

You are updating Facilities-NOID based on a match on Structure ID occurring in Facilities-ID.