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

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

SeTechAsked:
Who is Participating?
 
RemcovCConnect With a Mentor Commented:
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
 
RemcovCCommented:
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
 
SeTechAuthor Commented:
Perfect! THat worked like a charm!
0
 
SeTechAuthor Commented:
Perfect! Thanks for your help!
0
 
RemcovCCommented:
Glad to be of service

Remco
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.