Update joined table

[danb]

Hi, I'm trying the following query but getting a 'Missing Operator' error before the 'FROM' when it's saved.

The table 'Shops' contains a load of shops - each with a postcode. The Postcode_a table contains a list of postcodes which have Northing and Easting values.

I need to pull the northing and easting values into the shops table...


UPDATE Shops SET Shops.Easting=Postcodes_a.Easting, Shops.Northing=Postcodes_a.Northing
FROM Shops
INNER JOIN Postcodes_a.Postcode ON Shops.Postcode
WHERE (Shops.Postcode=Postcode_a.Postcode);

What's wrong with my SQL?

Thanks for your time guys,

D

 

[namliam]

The proper way:

UPDATE FirstTable INNER JOIN
SecondTable ON FirstTable.ID = SecondTable.ID SET FirstTable.Field1 = Secondtable.Field1, firstTable.field2 = Secontabe.field2;

However why do it at all? You can allways use Postcode to rever to your east west north south thingy. Its not advisable to store something allready stored somewhere else...

Regards

 

[danb]

Thanks, works a treat!

FYI, the postcodes database is not actully being dynamically used on the website because it contains almost 2 million records. I'm joining all the necessary data into one small database so as to reduce processing upon user request...

Thanks again.

 

[namliam]

All that i said was "Its not advisable...."

If you have good reasons (like yours) you should do it... But... in general... Not advisable...

Regards