?
Solved

Update query fails; "Multi-part identifier could not be bound" message

Posted on 2008-10-01
5
Medium Priority
?
385 Views
Last Modified: 2012-08-13
I'm using SQL Server 2005. In a given database, there is a table that exists called tblACAgents. It has 5 columns : AgtNo, AL, GA, NC, SC. The "AgtNo" column is varchar(7) and the other 4 columns are all bits.

I imported an Excel spreadsheet into the database to create a second table, that I just left called "Sheet1_temp". It contains columns AgtNo, Pstate, and Bstate. The Pstate columns is irrelevant (just forgot to exclude it from the import). What's relevant is the AgtNo column and the Bstate column, which will contain one of the values for those 4 state abbreviations above (AL, GA, NC, or SC).

What I'm attempting to do is write a one-time-use query that will update tblACAgents where the AgtNo values match up, changing the bit for the state column displayed in the Bstate column from 0 to 1.

For example, if a given AgtNo's Bstate column reads 'GA', then I want to change the ACAGent GA column to 1 for that AgtNo.

I'm prefectly willing to just do it one state at a time, and have written a query that makes logical sense to me. I put it in the Code Snippet box below. The problem is that I'm getting the error message that "The multi-part identifier 'Sheet1_temp.AgtNo' could not be bound" and again for the Bstate column. From everything I've seen on Google, it looks like this is because I'm never truly accessing the Sheet1_temp table in a SELECT query. But I don't know how else to do this query. I've even tried using a CTE, but got the same error message.

So I'm basically at a standstill until I can get this figured out. Please see the code snippet below for my query. Thanks in advance for any help.
UPDATE tblACAgents
SET tblACAgents.GA = '1'
WHERE tblACAgents.AgtNo = Sheet1_temp.AgtNo AND Sheet1_temp.Bstate = 'GA'

Open in new window

0
Comment
Question by:SeTech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:RemcovC
ID: 22615865
I think this should do the trick:

UPDATE tblACAgents
INNER JOIN Sheet1_temp ON tblACAgents.AgtNo = Sheet1_temp.AgtNo
SET tblACAgents.GA = '1'
WHERE  Sheet1_temp.Bstate = 'GA'
0
 
LVL 6

Accepted Solution

by:
RemcovC earned 2000 total points
ID: 22615904
After some more consideration:

UPDATE tblACAgents
SET tblACAgents.GA = '1'
FROM tblACAgents
INNER JOIN Sheet1_temp ON tblACAgents.AgtNo = Sheet1_temp.AgtNo
WHERE  Sheet1_temp.Bstate = 'GA'
0
 

Author Closing Comment

by:SeTech
ID: 31502023
Perfect! THat worked like a charm!
0
 

Author Comment

by:SeTech
ID: 22616054
Perfect! Thanks for your help!
0
 
LVL 6

Expert Comment

by:RemcovC
ID: 22616391
Glad to be of service

Remco
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question