Solved

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

Posted on 2008-10-01
5
378 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
  • 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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now