Syntax Error Updating SQL Server Table


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 27

Thread: Syntax Error Updating SQL Server Table

  1. #1
    Ralf Guest

    Syntax Error Updating SQL Server Table


    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!



  2. #2
    Rune Bivrin Guest

    Re: Syntax Error Updating SQL Server Table

    "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

  3. #3
    kevin knudson Guest

    Re: Syntax Error Updating SQL Server Table


    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!
    >
    >



  4. #4
    Rune Bivrin Guest

    Re: Syntax Error Updating SQL Server Table

    "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

  5. #5
    Ralf Guest

    Re: Syntax Error Updating SQL Server Table


    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



  6. #6
    MarkN Guest

    Re: Syntax Error Updating SQL Server Table


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



  7. #7
    MarkN Guest

    Re: Syntax Error Updating SQL Server Table


    >
    >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.


  8. #8
    Rune Bivrin Guest

    Re: Syntax Error Updating SQL Server Table

    "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

  9. #9
    Rune Bivrin Guest

    Re: Syntax Error Updating SQL Server Table

    "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

  10. #10
    MarkN Guest

    Re: Syntax Error Updating SQL Server Table


    >
    >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.



  11. #11
    kevin knudson Guest

    Re: Syntax Error Updating SQL Server Table


    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



  12. #12
    Markn Guest

    Re: Syntax Error Updating SQL Server Table


    >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).


  13. #13
    MarkN Guest

    Re: Syntax Error Updating SQL Server Table


    >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

    >



  14. #14
    Rune Bivrin Guest

    Re: Syntax Error Updating SQL Server Table

    "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.

  15. #15
    Rune Bivrin Guest

    Re: Syntax Error Updating SQL Server Table

    "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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center