Syntax Error Updating SQL Server Table

[Ralf]

The following SQL statement works great in Access, but not in SQL Server:

UPDATE inv_decrement INNER JOIN inv_test2 ON inv_decrement.itemnumber=inv_test2.itemnumber
SET inv_test2.inventoryavailable = (inv_test2.inventoryavailable-inv_decrement.inventoryavailable);

(SQL Server Error: Incorrect syntax near the keyword 'INNER' )

In both the Access and SQL Server databases, I created two identically structured
tables with a primary key on .itemnumber. When I run the above statement
in Access, the records in inv_test2 are updated properly with the corresponding
records in inv_decrement (decrements inventory counts). In SQL Server, I
get a syntax error.

Any ideas? Is UPDATE a valid SQL Server keyword?

Thanks!

[Rune Bivrin]

"Ralf" <patsoft@ndak.net> wrote in news:3d4ea5e4$1@10.1.10.29:

>
> The following SQL statement works great in Access, but not in SQL
> Server:
>
> UPDATE inv_decrement INNER JOIN inv_test2 ON
> inv_decrement.itemnumber=inv_test2.itemnumber SET
> inv_test2.inventoryavailable =
> (inv_test2.inventoryavailable-inv_decrement.inventoryavailable);
>
> (SQL Server Error: Incorrect syntax near the keyword 'INNER' )
>

Try this:

UPDATE inv_decrement
SET inv_test2.inventoryavailable =
(inv_test2.inventoryavailable-inv_decrement.inventoryavailable)
FROM inv_decrement
INNER JOIN inv_test2
ON inv_decrement.itemnumber=inv_test2.itemnumber

Isn't SQL a beatiful standard?

Rune Bivrin

[kevin knudson]

Try


Update inv_test2
SET inv_test2.inventoryavailable = inv_test2.inventoryavailable -
(SELECT inv_decrement.inventoryavailable FROM inventoryavailable
WHERE inv_decrement.itemnumber = inv_test2.itemnumber);

The syntax doesn't look right to start, and I don't think you can update
with a join.

KlK, MCSE



"Ralf" <patsoft@ndak.net> wrote:
>
>The following SQL statement works great in Access, but not in SQL Server:
>
>UPDATE inv_decrement INNER JOIN inv_test2 ON inv_decrement.itemnumber=inv_test2.itemnumber
>SET inv_test2.inventoryavailable = (inv_test2.inventoryavailable-inv_decrement.inventoryavailable);
>
>(SQL Server Error: Incorrect syntax near the keyword 'INNER' )
>
>In both the Access and SQL Server databases, I created two identically structured
>tables with a primary key on .itemnumber. When I run the above statement
>in Access, the records in inv_test2 are updated properly with the corresponding
>records in inv_decrement (decrements inventory counts). In SQL Server, I
>get a syntax error.
>
>Any ideas? Is UPDATE a valid SQL Server keyword?
>
>Thanks!
>
>

[Rune Bivrin]

"kevin knudson" <klk@DontEmailMe.com> wrote in
news:3d4eb906$1@10.1.10.29:

>
> Try
>
>
> Update inv_test2
> SET inv_test2.inventoryavailable = inv_test2.inventoryavailable -
> (SELECT inv_decrement.inventoryavailable FROM inventoryavailable
> WHERE inv_decrement.itemnumber = inv_test2.itemnumber);
>
> The syntax doesn't look right to start, and I don't think you can
> update with a join.
>
> KlK, MCSE

Oh yes, you can! Books online is a treasure trove of information, y'know.

Rune Bivrin

[Ralf]

Thank you Rune! Your suggestion worked! Amazing how just moving a statement
around can make such a difference, and how stupid it is for Access to work
so differently! (the original SQL was created in Query Design in Access).

Thanks again,

Ralf.



Rune Bivrin <rune@bivrin.com> wrote:
>"kevin knudson" <klk@DontEmailMe.com> wrote in
>news:3d4eb906$1@10.1.10.29:
>
>>
>> Try
>>
>>
>> Update inv_test2
>> SET inv_test2.inventoryavailable = inv_test2.inventoryavailable -
>> (SELECT inv_decrement.inventoryavailable FROM inventoryavailable
>> WHERE inv_decrement.itemnumber = inv_test2.itemnumber);
>>
>> The syntax doesn't look right to start, and I don't think you can
>> update with a join.
>>
>> KlK, MCSE
>
>Oh yes, you can! Books online is a treasure trove of information, y'know.
>
>Rune Bivrin

[MarkN]

So it begs the question - Is Access SQL not SQL standard or is the statement
that works not standard SQL?

[MarkN]

>
>Oh yes, you can! Books online is a treasure trove of information, y'know.
>
>Rune Bivrin

No reason to be snotty. Kevin is pretty smart and works with multiple platforms.
He does lots of research as do I. But know one can know everything. I
would have came up with same answer because of the way I do my joins. Not
that I haven't done joins this way. Thanks for providing an example on how
to do this.

I would also say that Books Online (or any other tool like that) is also
a mine field to those who don't know any better. Tread carefully while
searching for treasure.

[Rune Bivrin]

"MarkN" <m@N.com> wrote in news:3d4fc2a4$1@10.1.10.29:

>
>>
>>Oh yes, you can! Books online is a treasure trove of information,
>>y'know.
>>
>>Rune Bivrin
>
> No reason to be snotty. Kevin is pretty smart and works with multiple
> platforms.
> He does lots of research as do I. But know one can know everything.
> I
> would have came up with same answer because of the way I do my joins.
> Not that I haven't done joins this way. Thanks for providing an
> example on how to do this.
>
> I would also say that Books Online (or any other tool like that) is
> also a mine field to those who don't know any better. Tread
> carefully while searching for treasure.
>
>

I wasn't trying to be snotty, although I understand it may come across that
way. However, I find it of slightly doubtful value to reply that something
can't be done, without being absolutly sure it can't. Hence the reference
to Books Online, as the syntax for UPDATE is readily available.

I agree though, that it's slightly, errr..., opaque. It's obvious you can
use a FROM clause somehow. How that is done isn't quite as clear.

Rune Bivrin

[Rune Bivrin]

"MarkN" <m@n.com> wrote in news:3d4fc125$1@10.1.10.29:

>
> So it begs the question - Is Access SQL not SQL standard or is the
> statement that works not standard SQL?
>
>

Not quite sure 'bout that one. My guess is *neither*, but that depends on
which standard you look in, I suppose.

Standards are great - everybody should have one!

Rune Bivrin

[MarkN]

>
>Not quite sure 'bout that one.

I don't know either. I haven't studied ANSI SQL.

>My guess is *neither*, but that depends on
>which standard you look in, I suppose.

Then how can they be 'standards'? That really would be an implementation
or a variation or vendor specific code.

>
>Standards are great - everybody should have one!

You must love MS products.

[kevin knudson]

No offense takin, I have a pretty thick skin. And have learned "there are
many ways to skin a cat".

Thanks for the vote of confidence though, Mark.

MSAccess SQL is not quite SQL, it is it's own variant, as are all SQLs really.
Although the later versions allow much more compatible SQL than priors.
Look at the actual JOIN syntax in Access is := ...

Personally I still prefer the sub select I wrote to a join, just seems clearer.
I'll have to push them through Query Analyzer later and see which has the
better performance.


KlK, MCSE



Rune Bivrin <rune@bivrin.com> wrote:
>"MarkN" <m@N.com> wrote in news:3d4fc2a4$1@10.1.10.29:
>
>>
>>>
>>>Oh yes, you can! Books online is a treasure trove of information,
>>>y'know.
>>>
>>>Rune Bivrin
>>
>> No reason to be snotty. Kevin is pretty smart and works with multiple
>> platforms.
>> He does lots of research as do I. But know one can know everything.

>> I
>> would have came up with same answer because of the way I do my joins.

>> Not that I haven't done joins this way. Thanks for providing an
>> example on how to do this.
>>
>> I would also say that Books Online (or any other tool like that) is
>> also a mine field to those who don't know any better. Tread
>> carefully while searching for treasure.
>>
>>
>
>I wasn't trying to be snotty, although I understand it may come across that

>way. However, I find it of slightly doubtful value to reply that something

>can't be done, without being absolutly sure it can't. Hence the reference

>to Books Online, as the syntax for UPDATE is readily available.
>
>I agree though, that it's slightly, errr..., opaque. It's obvious you can

>use a FROM clause somehow. How that is done isn't quite as clear.
>
>Rune Bivrin

[Markn]

>I wasn't trying to be snotty,

Ok. It is oft times difficult to communicate via the 'written' word. Many
a time I have started an email, killed it and made a phone call.



>although I understand it may come across that
>way. However, I find it of slightly doubtful value to reply that something

>can't be done, without being absolutly sure it can't.

First - He said he thought. Not that he knew. No absolute there.

Second. I agree that unless one is absolutely sure ... . That is why I
question people when they say something like "We will never use anything
but Windows."

Third. It is difficult(not saying impossible - thinking it yes) to prove
the lack of the existence of anything. Not without some presuppositions.


>Hence the reference
>to Books Online, as the syntax for UPDATE is readily available.

We've both probably written alot of SQL. Kevin probably a lot before SQL
Server was a twinkle in Bill's eye. Why would he check something so simple
as an Update statement in Online Books? (Ok, now we have a reason)

>
>I agree though, that it's slightly, errr..., opaque. It's obvious you can

>use a FROM clause somehow. How that is done isn't quite as clear.

This one is not so bad and actually maybe better. But when one joins more
than 2 tables and has more where statments and more fields then it becomes
a jumble. Plus, it is usually more to type when one is handcoding SQL (Typing
- one of my Achilles heels).

[MarkN]

>Thanks for the vote of confidence though, Mark.

No problem. Glad you have one ("pretty thick skin"). Me - I'm sensitive
(my wife would say something stronger - but that would hurt my feelings
)

We're not perfect. But sometimes ...

To me, clearer is more important than a little speed. If totally necessary,
go for speed and comment it. Usually a little tuning, running the data access
closer to the db and connection pooling are enough.

Mark

"kevin knudson" <klk@knudsons.com> wrote:
>
>No offense takin, I have a pretty thick skin. And have learned "there are
>many ways to skin a cat".
>
>Thanks for the vote of confidence though, Mark.
>
>MSAccess SQL is not quite SQL, it is it's own variant, as are all SQLs really.
> Although the later versions allow much more compatible SQL than priors.
> Look at the actual JOIN syntax in Access is := ...
>
>Personally I still prefer the sub select I wrote to a join, just seems clearer.
> I'll have to push them through Query Analyzer later and see which has the
>better performance.
>
>
>KlK, MCSE
>
>
>
>Rune Bivrin <rune@bivrin.com> wrote:
>>"MarkN" <m@N.com> wrote in news:3d4fc2a4$1@10.1.10.29:
>>
>>>
>>>>
>>>>Oh yes, you can! Books online is a treasure trove of information,
>>>>y'know.
>>>>
>>>>Rune Bivrin
>>>
>>> No reason to be snotty. Kevin is pretty smart and works with multiple
>>> platforms.
>>> He does lots of research as do I. But know one can know everything.
>
>>> I
>>> would have came up with same answer because of the way I do my joins.
>
>>> Not that I haven't done joins this way. Thanks for providing an
>>> example on how to do this.
>>>
>>> I would also say that Books Online (or any other tool like that) is
>>> also a mine field to those who don't know any better. Tread
>>> carefully while searching for treasure.
>>>
>>>
>>
>>I wasn't trying to be snotty, although I understand it may come across
that
>
>>way. However, I find it of slightly doubtful value to reply that something
>
>>can't be done, without being absolutly sure it can't. Hence the reference
>
>>to Books Online, as the syntax for UPDATE is readily available.
>>
>>I agree though, that it's slightly, errr..., opaque. It's obvious you can
>
>>use a FROM clause somehow. How that is done isn't quite as clear.
>>
>>Rune Bivrin
>

[Rune Bivrin]

"MarkN" <m@N.com> wrote in news:3d4fec12$1@10.1.10.29:

>
>>
>>Not quite sure 'bout that one.
>
> I don't know either. I haven't studied ANSI SQL.
>
Which version of ANSI SQL is it you haven't studied?
Seriously, though, I tried to look at ANSI-92, and it didn't seem to be in
there. Doesn't seem like SQL3 has anything to do with core SQL syntax.

>>
>>Standards are great - everybody should have one!
>
> You must love MS products.
>
>
And every other commercial software vendor, I suppose. What would be the
value of products from multiple vendors, if they all followed the standard,
and didn't extend it? Would be a bit like ice cream vendors just selling
vanilla or chocolate, I think.

[Rune Bivrin]

"kevin knudson" <klk@knudsons.com> wrote in news:3d4fed4d$1@10.1.10.29:

>
> No offense takin, I have a pretty thick skin. And have learned "there
> are many ways to skin a cat".
>
> Thanks for the vote of confidence though, Mark.
>
> MSAccess SQL is not quite SQL, it is it's own variant, as are all SQLs
> really.
> Although the later versions allow much more compatible SQL than
> priors. Look at the actual JOIN syntax in Access is := ...
>
> Personally I still prefer the sub select I wrote to a join, just seems
> clearer.
> I'll have to push them through Query Analyzer later and see which has
> the
> better performance.
>
>
> KlK, MCSE
>
I agree that the sub select is easier to read. There arises a slight
complication though, when you want to update more than one field. It's
gonna be an awful lot of sub selects, don't you think? Or am I being dense
now? (Quite possible, I assure you!)

Rune Bivrin