September 24, 2012 at 10:05 am
Hi,
I'm a newbie and this is my first post.
I'm trying to update a table based on a select query, but I'm getting the following error message when I try to execute the script (it parses fine).
"Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "bhc.coreprocesstoken18" could not be bound."
Here's the script:
Update ContractData
set cd.coreprocesstoken18 = udb.coreprocesstoken18
FROM ContractData AS cd
INNER JOIN
(select accountnumber, sourcesystem, effectivedate, siccode, coreprocesstoken18
from RR_GMC.RCA.dbo.UDBSourceData
where sourcesystem = 'CAN'
and effectivedate = '6/30/2012') as udb ON udb.accountnumber = cd.accountnumber
What I'm trying to do is select data from table UDBSourceData in db RCA on linked server RR_GMC, and then update the column coreprocesstoken18 in table ContractData using the value in the same column from UDBSourceData, for the accountnumbers that match.
I've looked at other topics with the same error, but I haven't been able to figure out how to fix this.
Any help in explaining the error I'm making will be much appreciated.
September 24, 2012 at 10:16 am
SQL_beginner1 (9/24/2012)
Hi,I'm a newbie and this is my first post.
I'm trying to update a table based on a select query, but I'm getting the following error message when I try to execute the script (it parses fine).
"Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "bhc.coreprocesstoken18" could not be bound."
Here's the script:
Update ContractData
set cd.coreprocesstoken18 = udb.coreprocesstoken18
FROM ContractData AS cd
INNER JOIN
(select accountnumber, sourcesystem, effectivedate, siccode, coreprocesstoken18
from RR_GMC.RCA.dbo.UDBSourceData
where sourcesystem = 'CAN'
and effectivedate = '6/30/2012') as udb ON udb.accountnumber = cd.accountnumber
What I'm trying to do is select data from table UDBSourceData in db RCA on linked server RR_GMC, and then update the column coreprocesstoken18 in table ContractData using the value in the same column from UDBSourceData, for the accountnumbers that match.
I've looked at other topics with the same error, but I haven't been able to figure out how to fix this.
Any help in explaining the error I'm making will be much appreciated.
Is this a view or a table?
RR_GMC.RCA.dbo.UDBSourceData
September 24, 2012 at 10:18 am
Is there a trigger on that table?
That particular piece of code can't throw that error, as bhc.coreprocesstoken18 appears nowhere in the update statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2012 at 10:21 am
I too suspect this is a view. What does this return?
select top 1 *
FROM ContractData AS cd
INNER JOIN
(select accountnumber, sourcesystem, effectivedate, siccode, coreprocesstoken18
from RR_GMC.RCA.dbo.UDBSourceData
where sourcesystem = 'CAN'
and effectivedate = '6/30/2012') as udb ON udb.accountnumber = cd.accountnumber
I have a feeling your error is going to be there and the error is really in your view "UDBSourceData".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 24, 2012 at 10:30 am
Is this a view or a table?
RR_GMC.RCA.dbo.UDBSourceData
Lynn, it's a table.
Both the source and the destination tables have the same columns, and I need to update the column coreprocesstoken18 in destination table (ContractData) for the condition given in the code.
September 24, 2012 at 10:32 am
SQL_beginner1 (9/24/2012)
Is this a view or a table?
RR_GMC.RCA.dbo.UDBSourceData
Lynn, it's a table.
Both the source and the destination tables have the same columns, and I need to update the column coreprocesstoken18 in destination table (ContractData) for the condition given in the code.
Then as Gail asked, is there a trigger on the ContractData table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 24, 2012 at 10:34 am
Every object in the database has 4 parts to his name. Some of the parts have default, so most times we don't use all the parts. Suppose that I have a server that is called MyServer. On the server there is a database that is called MyDB. In the database there is a schema that is called MySchema and it has a table that is called MyTable. The table's full name is – MyServer.MyDB.MySchema.MyTable. If I'm working on the same server and I don't use linked serve, I can omit the part the specifies the server name and work only with the database's name part, schema's name part and object's name part – MyDB.MySchema.MyTable. If I want to reference an object that is located on the database that I'm connected to, I can also omit the database's name and use only the schema's name and the table's name – MySchema.MyTable. If my default schema is MySchema, I can also omit the schema's name and use only the table's name – MyTable, but this is not recommended.
The error that you get says that your query is referencing a column that is called coreprocesstoken18 on table that is called (or aliased on the query) as bhc, but it can't find such table or alias in the query. Since I don't see that have this table nor this alias in your code, it could be one of the fallowing
1)You copied only part of the code, or the wrong code
2)You have an update trigger on ContractData and the error is generated from the trigger.
3)One of the object that you working with is a view and the view has if referencing a table that doesn't exists in the from clause.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 24, 2012 at 10:34 am
Sean Lange (9/24/2012)
SQL_beginner1 (9/24/2012)
Is this a view or a table?
RR_GMC.RCA.dbo.UDBSourceData
Lynn, it's a table.
Both the source and the destination tables have the same columns, and I need to update the column coreprocesstoken18 in destination table (ContractData) for the condition given in the code.
Then as Gail asked, is there a trigger on the ContractData table?
In agreement here as well now.
September 24, 2012 at 10:38 am
Sean, the code you provided returned one row of data that included all the columns from table ContractData; and accountnumber, sourcesystem, effectivedate, siccode & coreprocesstoken18 from UDBSourceData for the same accountnumber. I cannot post the result here as there are 250+ columns.
September 24, 2012 at 10:44 am
SQL_beginner1 (9/24/2012)
Sean, the code you provided returned one row of data that included all the columns from table ContractData; and accountnumber, sourcesystem, effectivedate, siccode & coreprocesstoken18 from UDBSourceData for the same accountnumber. I cannot post the result here as there are 250+ columns.
No worries. Just wanted to see if the naming convention was ok, which apparently it is. That means there is little doubt left that there MUST be a trigger on ContractData.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 24, 2012 at 10:46 am
Gail,
No triggers on the table ContractData.
"..bhc.coreprocesstoken18 appears nowhere in the update statement."
Oops, sorry about that - I'd changed the alias in the code, but posted the earlier error message from a different window.
The error message is
"Msg 4104, Level 16, State 1, Line 1. The multi-part identifier "cd.coreprocesstoken18" could not be bound."
Apologies!
September 24, 2012 at 10:57 am
Try this:
Update cd
set coreprocesstoken18 = udb.coreprocesstoken18
FROM ContractData AS cd
INNER JOIN
(select accountnumber, sourcesystem, effectivedate, siccode, coreprocesstoken18
from RR_GMC.RCA.dbo.UDBSourceData
where sourcesystem = 'CAN'
and effectivedate = '6/30/2012') as udb ON udb.accountnumber = cd.accountnumber
September 24, 2012 at 11:02 am
Oh I think I see your error. You fully qualified the column to update which I think will not work. If Lynn's does not work try this one. Although he also removed the qualifier on the column 😉
Update ContractData
set cd.coreprocesstoken18 = udb.coreprocesstoken18
FROM ContractData AS cd
INNER JOIN
(select accountnumber, sourcesystem, effectivedate, siccode, coreprocesstoken18
from RR_GMC.RCA.dbo.UDBSourceData
where sourcesystem = 'CAN'
and effectivedate = '6/30/2012') as udb ON udb.accountnumber = cd.accountnumber
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 24, 2012 at 12:45 pm
Thanks, Lynn and Sean.
Yes, that worked!! 🙂
So can I take way the following as a rule?
"Don't fully qualified the column that needs to be updated (in a scenario like this)."
Thanks, again! I appreciate the help.
September 24, 2012 at 12:53 pm
SQL_beginner1 (9/24/2012)
Thanks, Lynn and Sean.Yes, that worked!! 🙂
So can I take way the following as a rule?
"Don't fully qualified the column that needs to be updated (in a scenario like this)."
Thanks, again! I appreciate the help.
Well there are a couple things going on here. First the Update From is sql server specific. I don't think other RDBMS have that. But yes that is correct. You specify which table you want to update. That means any column in the update MUST refer to a column in that table. Apparently the engine can't even figure it out when you do qualify the column like you did.
Thanks for letting us know that worked.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply