SQL Update a table from another table in a different database

Hello Experts;

I am trying to UPDATE a table named 'ORDERS' from another table also named 'ORDERS' in a different database. Both databases are on the same MS SQL 2008r2 server.

The Query is;
UPDATE SRSCustom1.dbo.ORDERS
SET
ORD_ID = SRSPRM2.dbo.ORDERS.ORD_ID                      
FROM
SRSPRM2.dbo.ORDERS
WHERE
ORD_ID = SRSPRM2.dbo.ORDERS.ORD_ID
AND
SRSCustom1.ORD_STATUS='O'

I'm getting an error;
"The objects "SRSCustom1.dbo.ORDERS" and "SRSPRM2.dbo.ORDERS" in the FROM clause have the same exposed names. Use correlation names to distinguish them."

When I add an Alias for the SRSPRM2.dbo.ORDERS table  AS 'SRSPRM' I get an error;
"Incorrect syntax near SRSPRM".

I need help in structuring the Query correctly.

Thank you,
Visionetv
visionetvAsked:
Who is Participating?
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.

Habib PourfardSoftware DeveloperCommented:
the following code should work:
UPDATE SRSCustom1.dbo.ORDERS
SET ORD_ID = SRSPRM2.dbo.ORDERS.ORD_ID
FROM SRSPRM2.dbo.ORDERS O2
WHERE ORD_ID = O2.ORD_ID
AND ORD_STATUS = 'O'

Open in new window

0
visionetvAuthor Commented:
Hi Habib;

Thank you for your response. I am now getting the following errors using the query you sent;

Ambiguous column name 'ORD_ID'.
Ambiguous column name 'ORD_STATUS'.
0
Habib PourfardSoftware DeveloperCommented:
UPDATE SRSCustom1.dbo.ORDERS
SET ORD_ID = SRSPRM2.dbo.ORDERS.ORD_ID
FROM SRSPRM2.dbo.ORDERS O2
WHERE SRSCustom1.dbo.ORD_ID = O2.ORD_ID
AND SRSCustom1.dbo.ORD_STATUS = 'O'

Open in new window

0
SharathData EngineerCommented:
You joined two tables on ORD_ID and trying to update the same column? Is that what you want?
try this
UPDATE s1
   SET s1.ORD_ID = s2.ORD_ID
  FROM SRSCustom1.dbo.ORDERS s1
  JOIN SRSPRM2.dbo.ORDERS s2
    ON s1.ORD_ID = s2.ORD_ID
 WHERE s1.ORD_STATUS = 'O'

Open in new window

0

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
visionetvAuthor Commented:
Hi Sharath;

Thank you for the query structure as it solved my question about how to place an alias in an Update statement.

Visionetv
0
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
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.