what's wrong with this sql statement?

  • Thread starter Thread starter dlw
  • Start date Start date
D

dlw

getting missing operator on this:
UPDATE CPcosts INNER JOIN [INMAST-test] ON [CPcosts].stockno =
[INMAST-test].stockno
SET [INMAST-test].sellprice = [CPcosts]![price]
SET [INMAST-test].calcsellprice = [CPcosts]![price]
SET [INMAST-test].currentcost = [CPcosts]![cost]
SET [INMAST-test].totalcost = [CPcosts]![cost] * [INMAST-test].onhand;

Can I do multiple SETs like that?
Thank you for you help.
 
This looks like a query for Access JET-SQL and not for T-SQL, the version
used on SQL-Server; which, BTW, is the subject of this newsgroup. There are
differences between JET-SQL and T-SQL, so I'm not sure if the following not
is true:

For multiple SET, you must use a single SET statement and separate the
multiple sets with comma:

UPDATE CPcosts INNER JOIN [INMAST-test] ON [CPcosts].stockno =
[INMAST-test].stockno
SET [INMAST-test].sellprice = [CPcosts]![price],
[INMAST-test].calcsellprice = [CPcosts]![price],
[INMAST-test].currentcost = [CPcosts]![cost],
[INMAST-test].totalcost = [CPcosts]![cost] * [INMAST-test].onhand;

Hope this can help.
 
Yes, it is old access 2000 jet, and it's working now- THANKS!

Sylvain Lafontaine said:
This looks like a query for Access JET-SQL and not for T-SQL, the version
used on SQL-Server; which, BTW, is the subject of this newsgroup. There are
differences between JET-SQL and T-SQL, so I'm not sure if the following not
is true:

For multiple SET, you must use a single SET statement and separate the
multiple sets with comma:

UPDATE CPcosts INNER JOIN [INMAST-test] ON [CPcosts].stockno =
[INMAST-test].stockno
SET [INMAST-test].sellprice = [CPcosts]![price],
[INMAST-test].calcsellprice = [CPcosts]![price],
[INMAST-test].currentcost = [CPcosts]![cost],
[INMAST-test].totalcost = [CPcosts]![cost] * [INMAST-test].onhand;

Hope this can help.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


dlw said:
getting missing operator on this:
UPDATE CPcosts INNER JOIN [INMAST-test] ON [CPcosts].stockno =
[INMAST-test].stockno
SET [INMAST-test].sellprice = [CPcosts]![price]
SET [INMAST-test].calcsellprice = [CPcosts]![price]
SET [INMAST-test].currentcost = [CPcosts]![cost]
SET [INMAST-test].totalcost = [CPcosts]![cost] * [INMAST-test].onhand;

Can I do multiple SETs like that?
Thank you for you help.
Click to expand...
Click to expand...
 
Back
Top