SQL Update from MS Access

[SimonT]

Hi Guys,

I need to re-write this update in SQL, this is the SQL taken from my Access
system that I am upgrading to a SQL BE

UPDATE tblgoodsinlineitems
INNER JOIN products
ON tblgoodsinlineitems.productID = products.productID
SET products.cost = [tblgoodsinlineitems].[cost];

When I try this is SQL I get this error message:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'INNER'.

Do I need to include the scheme as part of the update?

Sorry to cross post but unsure which is best forum to post this question on

Thanks
Si

[Sylvain Lafontaine]

There is a difference of syntaxe between JET/SQL and T-SQL: you must move
the JOIN below the SET; something like:

UPDATE products

SET products.cost = [tblgoodsinlineitems].[cost]

FROM tblgoodsinlineitems INNER JOIN products
ON tblgoodsinlineitems.productID = products.productID

or more clearly:

UPDATE products

SET products.cost = [tblgoodsinlineitems].[cost]

FROM products INNER JOIN tblgoodsinlineitems
ON products.productID = tblgoodsinlineitems.productID

or with aliases:

UPDATE p
SET p.cost = t.[cost]
FROM products p INNER JOIN tblgoodsinlineitems t
ON p.productID = t.productID

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"SimonT" <Si_tyler@mymail_hotmail.co.uk> wrote in message
news:Oy142FD0...@TK2MSFTNGP02.phx.gbl...

[SimonT]

Cool, Ok that's a big help

many thanks

"Sylvain Lafontaine" <sylvainlaf...@yahoo.ca> wrote in message
news:%23Kor71D...@TK2MSFTNGP05.phx.gbl...