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)

Post by 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];
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

[SimonT]

Cool, Ok that's a big help

many thanks

Post by Sylvain Lafontaine
There is a difference of syntaxe between JET/SQL and T-SQL: you must move
UPDATE products
SET products.cost = [tblgoodsinlineitems].[cost]
FROM tblgoodsinlineitems INNER JOIN products
ON tblgoodsinlineitems.productID = products.productID
UPDATE products
SET products.cost = [tblgoodsinlineitems].[cost]
FROM products INNER JOIN tblgoodsinlineitems
ON products.productID = tblgoodsinlineitems.productID
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)

Post by 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];
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