Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2013
    Posts
    5

    Unanswered: Converting Converting Server Update query to Access compatible Update Query

    Hi,

    I have an Update SQL query, which works in MS SQL Server.
    The tables concerned are dbo_Contacts which has a Primary Key ContactID and is linked to another table dbo_Location with a foreign key LocationID.
    There are columns in both these tables called Current, which is datatype char(1) and holds either "Y" or "N" for is current or is not current.

    Now I have a process that updates the Current column in Locations table. I need to update all the corresponding Contacts by joining on the Foreign Key, LocationID, that are marked dbo_Locations.[Current] = "N" in the Locations table.

    The Query is as follows.

    update dbo_Contacts
    set dbo_Contacts.[Current] = 'N'
    from dbo_Contacts c
    inner join dbo_Locations l on c.LocationID = l.LocationID
    where l.[Current] = 'N'
    and c.FirstName is not null
    and c.LastName is not null

    I am trying to use this query in Access 2010. The issue is I'm getting a syntax error:

    Syntax error (missing operator) in query Expression "N'
    from
    dbo_Contacts c
    inner join
    dbo_Locations l
    on
    c.LocationID = l.LocationID'.


    Any sort of help is more than welcome and thank you is advance!

    -Avi

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    What datatype is the column current.
    It could be you need to use "N"
    If its boolean use true or false
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2013
    Posts
    5
    Thank you for your help!
    The tables in access are linked to tables in SQL Server. The Datatype for the column current for both tables in SQL Server, Locations and Contacts, are char(1) and are both nullable fields.

    The datatypes for those linked tables in access are Text with Field size as 1 and the Required field set as No.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looking at your SQL in a bit more detail
    im suspicious of the [quote]from dbo_Contacts c[quote]

    Code:
    update dbo_Contacts
    set dbo_Contacts.[Current] = 'N'
    inner join dbo_Locations on dbo_Contacts.LocationID = dbo_Locations.LocationID
    where l.[Current] = 'N' 
    and c.FirstName is not null 
    and c.LastName is not null
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2013
    Posts
    5
    Hmm I'm not so sure that will run in SQL or Access. Might it be the join needs to happen in the Update clause and not after the set clause?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well if the original form is OK it could be the string literal should be "N" not 'N'

    as this is a SQL Server queation do you want it moving to the SQL Server thread
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2013
    Posts
    5
    Sure. Thanks.

  8. #8
    Join Date
    Mar 2013
    Posts
    5
    And I tried "N". No such luck.

  9. #9
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I'm confused. The query you are asking about has changed in the retelling, so let's go back to the original. You are saying that the following does not work:

    Code:
    update dbo_Contacts
    set dbo_Contacts.[Current] = 'N'
    from dbo_Contacts c
    inner join dbo_Locations l on c.LocationID = l.LocationID
    where l.[Current] = 'N' 
    and c.FirstName is not null 
    and c.LastName is not null
    For SQL Server, it should be:

    Code:
    update  c
    set     c.[Current] = 'N'
    --select  *
    from    dbo_Contacts c
    inner
    join    dbo_Locations l
                on c.LocationID = l.LocationID
    where   l.[Current] = 'N' 
            and c.FirstName is not null 
            and c.LastName is not null
    Once you alias a table name in the FROM clause, you should be consistent and always refer to the alias, not the original object name.

    Notice my commented-out SELECT *. It's handy to have, so you can test and debug your select criteria while you are developing your query.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •