June 9, 2011 at 6:05 pm
I am going to punch myself in the face. I have tried everything to get this to work and I keep getting the same error.
use AndarProduction;
update CoCNewAndarUsersImport set CoCNewAndarUsersImport.ORGACCOUNTNUMBER=[existingorgimporttable].[Accountnumber]
where CoCNewAndarUsersImport.ORGNAME=[existingorgimporttable].[ExistingOrg];
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "existingorgimporttable.ExistingOrg" could not be bound.
I have tried using the 4 part, 3 part, 2 part and just the table identifier. What is even weirder, is that even when I write quieries that work like:
select existingorgimporttable.ExistingOrg from existingorgimporttable;
It still says it invalid object name and underlines the table/column names in red but it returns results.
I have tried brackets, no brackets. Originally I was updating a table with the values from a view that was in another database. I then moved the view and table into the same database, still the same error. I then converted the view to a table, which is what I have now, still the same error. I have restered SQL server and I am stuck, Please Help!
Thanks in advance.
June 10, 2011 at 6:10 am
update a
set a.ORGACCOUNTNUMBER=e.[Accountnumber]
from CoCNewAndarUsersImport a
join [existingorgimporttable] e
on a.ORGNAME=e.[ExistingOrg];
--tablename <> Alaia
June 10, 2011 at 10:34 am
Okay, that worked perfect. What was my issue? Was it because I wasn't joining the tables or did I need to alias each table or both? Would the following have worked?
update a
set a.ORGACCOUNTNUMBER=e.Accountnumber
from CoCNewAndarUsersImport a
join existingorgimporttable e
on a.ORGNAME=e.ExistingOrg;
Thank you so much for your help, I am just trying to understand where I went wrong!
June 10, 2011 at 11:42 am
JAYANTH KURUP gave you a solution but might I suggest you use MERGE instead. It looks strange at first but once you learn the syntax it becomes easy, plus it has the benefit of not suffering from some issues that can be brought about by issuing an UPDATE...JOIN where multiple rows qualify for the update.
-- untested because I do not have your DDL but you can see the idea
MERGE CoCNewAndarUsersImport AS target
USING
(
SELECT Accountnumber,
ExistingOrg
FROM CoCNewAndarUsersImport
) AS source (Accountnumber, ExistingOrg)
ON (target.ORGNAME = source.ExistingOrg)
WHEN MATCHED
THEN
UPDATE
SET ORGACCOUNTNUMBER = source.Accountnumber ;
Reference: http://technet.microsoft.com/en-us/library/bb510625.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 10, 2011 at 1:28 pm
I agree with ocp , if you can help it try and use Merge within the proc, exceptions could be where the proc is expected to perform simple crud and not expected to perform the insert anyway or if your query is expected to be ansi compatible or remotely executed or using some kind of ORM.
As for the reason for the issue , you specified the value to be used in the set operation but didnt specify the table. Without the table name as part of a join sql understands the tablename.column only as an alias and not as a qualified name and since it understood the alias, it was looking for the reference to the table within the query (which was not provided).
June 12, 2011 at 10:34 pm
Thank you Jayanth and OPC. The merge looks to be just what I need. I am relatively new to SQL, I took a few database courses throughout college that included 6 months of relational algebra, 6 months of DB design, and about 3 weeks of SQL.
Thanks again!
June 13, 2011 at 9:40 am
You're very welcome, thanks for the feedback...and welcome to the site 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply