June 27th, 2012, 05:43 PM
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:
I am getting an error that says :
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
Under the error list, I have:
Invalid Object Name
I'm a newb to SQL and am sure I'm just overlooking something simple. Thanks again!
June 27th, 2012, 06:06 PM
there are several things at play here
Originally Posted by boulderrr
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 --
SET microzone = t2.microzone
FROM [KS_SS_2012.05.31_RMS11_RDM]..Surge_Levels AS t1
JOIN [KS_SS_2012.05.31_RMS11_RDM]..Expacc_Loc AS t2
ON t2.LOCNUM = t1.LOCNUM
June 27th, 2012, 06:12 PM
Thanks so much and my apologies for posting in the wrong forum. I wasn't quite sure which one was the proper one.
Originally Posted by r937
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!