[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2008-10-01
5
Medium Priority
?
388 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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