Post reply

Update Ststement Syntax Error

  • August 25, 2009 at 7:42 am

    #213740

    Hello,

    I'm having trouble with this update query:

    UPDATE AWCWaterSystem.GISADMIN.ServicePoint P, AWCWaterSystem.GISADMIN.ServiceTap T

    SET P.AddressStreetNumber=T.AddressStreetNumber, P.AddressStreetName=T.AddressStreetName, P.AddressStreetType=T.AddressStreetType, P.AddressCity=T.AddressCity, P.AddressState=T.AddressState, P.AddressZipCode=T.AddressZipCode

    WHERE P.FeatureId=587160 and T.FeatureId=752058;

    I get the Msg 102 error stating that there's a syntax error near "P" in line 1.

    I originally built this query in a test database in Access where it works just fine, but in SQL Server (2005), I get this syntax error. Any ideas?

    Thanks in Advance,

    T. Servo

  • August 25, 2009 at 8:01 am

    #1044195

    In SQL, only one table may follow the UPDATE keyword.

    Take a look at Books Online for the UPDATE .. FROM syntax. Access doesn't have that form of the update statement.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 25, 2009 at 8:03 am

    #1044198

    It's been more years than I care to think about since I last used Access, so I can't comment on the code for use within that. However, there are quite a few errors when trying to use it within SQL Server.

    First of all, in order to provide a working solution I need to know what the relationship is between the two tables AWCWaterSystem.GISADMIN.ServicePoint and AWCWaterSystem.GISADMIN.ServiceTap.

    In other words, what is the key that connects the two? It can't be FeatureID, because even though the same fieldname exists in both tables it has different values.

    So what joins these two tables (it may even be another table)?

    BrainDonor.

    Steve Hall
    Linkedin
    Blog Site

  • August 25, 2009 at 8:20 am

    #1044213

    Thanks, using the UPDATE FROM structure was the answer. So my script now looks like this, and parses successfully:

    UPDATE GISADMIN.ServicePoint

    SET GISADMIN.ServicePoint.AddressStreetNumber=GISADMIN.ServiceTap.AddressStreetNumber

    FROM GISADMIN.ServicePoint

    INNER JOIN GISADMIN.ServiceTap

    ON (GISADMIN.ServicePoint.FeatureId=587160 and GISADMIN.ServiceTap.FeatureId=752058);

    Believe it or not, the previous way that I had this did work in Access, which is evidently very forgiving when it comes to this situation...

    Thanks for the help!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply