Update query..

Discussion in 'ASP General' started by Vincent Jones, Jan 27, 2004.

  1. I must be a dork!!!
    I can't figure this out.
    strQuery2 = "Update tbCard_Temp Set tbCard_Temp.Id='tbCardSwipe.Id
    From tbCardSwipe'"


    and i get this message
    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
    operator) in query expression 'tbCardSwipe.Id From tbCardSwipe'.
    Vincent Jones, Jan 27, 2004
    #1
    1. Advertising

  2. Vincent Jones

    Ray at Guest

    What are you trying to do? Your current query there is trying to set an ID
    to be this literal string: 'tbCardSwipe.Id From tbCardSwipe'

    Is that what you want? Or do you want something more like:

    Update tbCard_Temp SET ID=(SELECT TOP 1 [ID] FROM tbCardSwipe WHERE
    [something]='something') WHERE [something]='something'

    Ray at work



    "Vincent Jones" <> wrote in message
    news:...
    > I must be a dork!!!
    > I can't figure this out.
    > strQuery2 = "Update tbCard_Temp Set tbCard_Temp.Id='tbCardSwipe.Id
    > From tbCardSwipe'"
    >
    >
    > and i get this message
    > [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
    > operator) in query expression 'tbCardSwipe.Id From tbCardSwipe'.
    Ray at, Jan 27, 2004
    #2
    1. Advertising

  3. Maybe you meant:

    UPDATE tbCard_Temp SET Id =
    (SELECT tbCardSwipe.Id FROM tbCardSwipe)

    However, that would set *every* row in tbCard_Temp to some arbitrary id from
    tbCardSwipe. (Unless tbCardSwipe has exactly one row.)

    Sounds more like you meant to perform a join between the table to be updated
    and the table from which to derive the value. I'm not sure if Access
    supports updating from a join... in SQL Server, the syntax would be:

    UPDATE T
    SET T.Id = s.Id
    FROM tbCard_Temp T
    INNER JOIN tbCardSwipe s
    ON T.primary_key_col = s.primary_key_col

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/




    "Vincent Jones" <> wrote in message
    news:...
    > I must be a dork!!!
    > I can't figure this out.
    > strQuery2 = "Update tbCard_Temp Set tbCard_Temp.Id='tbCardSwipe.Id
    > From tbCardSwipe'"
    >
    >
    > and i get this message
    > [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
    > operator) in query expression 'tbCardSwipe.Id From tbCardSwipe'.
    Aaron Bertrand - MVP, Jan 27, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Learner
    Replies:
    1
    Views:
    973
    Marina Levit [MVP]
    Jan 30, 2006
  2. Anonymous
    Replies:
    0
    Views:
    1,439
    Anonymous
    Oct 13, 2005
  3. David Gordon

    xpath query query

    David Gordon, May 18, 2005, in forum: XML
    Replies:
    2
    Views:
    778
    David Gordon
    May 18, 2005
  4. Eric Nelson
    Replies:
    5
    Views:
    1,504
    Alexey Smirnov
    Feb 4, 2009
  5. David Shorthouse

    Update query doesn't update table

    David Shorthouse, Jun 17, 2005, in forum: ASP General
    Replies:
    6
    Views:
    245
    David Shorthouse
    Jun 17, 2005
Loading...

Share This Page