Solved

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

Posted on 2008-10-01
5
380 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.

Question has a verified solution.

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

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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