asked on 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
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
Microsoft SQL Server 2008SQL
Last Comment
visionetv
ASKER
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'.
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'.
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'
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hi Sharath;
Thank you for the query structure as it solved my question about how to place an alias in an Update statement.
Visionetv
Thank you for the query structure as it solved my question about how to place an alias in an Update statement.
Visionetv
Open in new window