Trying to run an update query in SQL Server using 2 tables from 2 attached databases


UPDATE db1.dbo.Post
SET db1.dbo.Post.GId = db1.dbo.routeGroup.SiteCode
FROM db1.dbo.Post
ON (db1.dbo.Post.CustomerId = db2.dbo.Meals_In.mls_prt_fk)

The result is all the references to db2 are underlined in red (invalid object name) even though I have verified both databases are attached and the names are correct.

Any suggestions anyone?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
The INNER JOIN is expecting a table or view name, even if it's a fully qualified database_name.schema_name.table_or_view_name, and it appears that we have  just the database name db2 here.   You can't only list the database name, as SQL has no idea what table you're talking about and what column(s) to join.

>SET db1.dbo.Post.GId = db1.dbo.routeGroup.SiteCode
The table routeGroup is not defined in your FROM block.  

Using table aliases would make this much easier to read
SpaceCoastLifeAuthor Commented:
Would you add your suggestions into my statement to make sure I understand?

As you can probably tell, I have very limited experience working with Sql Server - and I have no idea how to go about using aliases.

Would you also consider showing what the corrected version of my statement would look like with aliases? I'd very much appreciate it.
Jim HornMicrosoft SQL Server Data DudeCommented:
<air code, plus I'm making stuff up, so modify to suit your situation>

SET p.GId = rg.SiteCode
FROM db1.dbo.Post p
   JOIN db2.dbo.Meals mi ON p.CustomerId = mi.mls_prt_fk
   JOIN db1.dbo.routeGroup rg ON p.CustomerId = rg.CustomerId_fk -- ?? 

Open in new window

>and I have no idea how to go about using aliases.
When you use a table/view you can give it a 'nickname' that is shorter so that the query is much easier to read.
For example, instead of having to type db1.dbo.Post every time it's used, in the FROM clause I spell out the table name and then the nickname / alias of p, then everywhere in code I just use p.  p = Post, mi = Meals In, rg = Routh Group, you get the idea.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SpaceCoastLifeAuthor Commented:
Thanks for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.