I have a problem trying to write a query in SQL server 2005.

I want to update records in a table in one database from another table in a different database.
The code is below:

update orig.dbo.[credit]
    set orig.dbo.[credit].[limit] = tempdb.dbo.[customer_limits].[cr_limit_amt]
         from temp.dbo.[customer_limits]
    where temp.dbo.[customer_limits].[cr_acct_id] = orig.dbo.[credit].[legacy_id]

The code parses fine, but when I click the 'Execute' button, I get the error message below:

Msg 207, Level 16, State 1, Line 3
Invalid column name 'limit'.

Please help.
Thanks,
-Tim

ok..i overlooked my field names... I had a misspelling in the code vs the table. The field in the table is climit instead of limit. So after updating the code...it worked fine.

I just tried this on some of our databases and it worked, so I can only think of two reasons why it would not work for you.

1) you mispelled your column name
2) Try aliasing one side of your query, meaning:


update orig.dbo.[credit]
set orig.dbo.[credit].[limit] = t.[cr_limit_amt]
from temp.dbo.[customer_limits] t
where t.[cr_acct_id] = orig.dbo.[credit].[legacy_id]

or, you could alias both sides as well.

Let us know what happens.


========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================

update orig.dbo.[credit]
set orig.dbo.[credit].[limit] = t.[cr_limit_amt]
from temp.dbo.[customer_limits] t
where t.[cr_acct_id] = orig.dbo.[credit].[legacy_id]
 solve u problem