#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    4
    Rep Power
    0

    SQL Code Errors - Update Query with Inner Join (Newb)


    Hello and thanks in advance for any help!

    I was trying to do some table updating within Access, but the tables I'm dealing with are too large. Because of this, I decided to use MS SQL Server Management Studio so that it could be done using SQL code. I was able to formulate my base table, but forgot to add a particular column, so want to update this table now.

    Every table referenced below is contained within my database, which is titled KS_SS_2012.05.31_RMS11_RDM

    I have a table called Surge_Levels, which has most of my data. I forgot to include a column called MICROZONE. Using SQL Server Management Studio, I was able to add a new column called MICROZONE to the Surge_Levels table. This column is now in there, but is blank.

    I have another table within the database called Expacc_Loc, which has the information I need to update the MICROZONE column in the Surge_Levels table. To update the proper fields, I need to join a field in Surge_Levels called LOCNUM and the same field in Expacc_Loc called LOCNUM.

    From there, I simply need it to populate MICROZONE in Surge_Levels from what is in Expacc_Loc based on the LOCNUM field.

    To do this, I created what I wanted to do in Access, copied the SQL code over, and then did all the modifications I knew were necessary to make SQL run. It appears I must be missing something though, because it isn't working.

    This is what I have:

    PHP Code:
    UPDATE [KS_SS_2012.05.31_RMS11_RDM]..Expacc_Loc INNER JOIN [KS_SS_2012.05.31_RMS11_RDM]..Surge_Levels ON [KS_SS_2012.05.31_RMS11_RDM]..Expacc_Loc.LOCNUM = [KS_SS_2012.05.31_RMS11_RDM]..Surge_Levels.LOCNUM 
    I am getting an error that says :
    Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'INNER'.
    Under the error list, I have:
    Invalid Object Name
    'KS_SS_2012.05.31_RMS11_RDM..Expacc_Loc'.

    Incorrect syntax near 'INNER'.

    Incorrect syntax near 'ON'.
    I'm a newb to SQL and am sure I'm just overlooking something simple. Thanks again!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by boulderrr
    To do this, I created what I wanted to do in Access, copied the SQL code over, and then did all the modifications I knew were necessary to make SQL run. It appears I must be missing something though, because it isn't working.
    there are several things at play here

    first, you posted in the mysql forum, which is a completely different database system from microsoft sql server

    (don't worry, i've moved your thread over to the correct forum)

    second, microsoft sql server is different from microsoft access sql, you can't just run the same sql syntax

    this is the syntax you want --
    Code:
    UPDATE [KS_SS_2012.05.31_RMS11_RDM]..Surge_Levels 
       SET microzone = t2.microzone
      FROM [KS_SS_2012.05.31_RMS11_RDM]..Surge_Levels AS t1  
    INNER 
      JOIN [KS_SS_2012.05.31_RMS11_RDM]..Expacc_Loc AS t2  
        ON t2.LOCNUM = t1.LOCNUM
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    there are several things at play here

    first, you posted in the mysql forum, which is a completely different database system from microsoft sql server

    (don't worry, i've moved your thread over to the correct forum)

    second, microsoft sql server is different from microsoft access sql, you can't just run the same sql syntax

    this is the syntax you want --
    Code:
    UPDATE [KS_SS_2012.05.31_RMS11_RDM]..Surge_Levels 
       SET microzone = t2.microzone
      FROM [KS_SS_2012.05.31_RMS11_RDM]..Surge_Levels AS t1  
    INNER 
      JOIN [KS_SS_2012.05.31_RMS11_RDM]..Expacc_Loc AS t2  
        ON t2.LOCNUM = t1.LOCNUM
    Thanks so much and my apologies for posting in the wrong forum. I wasn't quite sure which one was the proper one.

    I did modify the code somewhat from Access prior to putting it into SQL (added bracketed DB name, etc), but it looks like I was a bit too simplistic with the transfer over. When I created the original table using SQL, I was able to mostly just transfer the Access SQL code with only minor modifications such as the aforementioned database name inclusion and the bracketing of names with dots in them. As you can tell, I've not ventured far enough into the wonderful world of SQL. Thanks again for your help!

IMN logo majestic logo threadwatch logo seochat tools logo