Column of links in a table on another server in UPDATE / JOIN (SQL Server)
I'm familiar with 4 pair naming, but I get an error every time I try to reference the column. For example:
UPDATE my_table
SET my_table.column1 = otherserver.otherdatabase.dbo.othertable.column1
FROM my_table INNER JOIN otherserver.otherdatabase.dbo.othertable
ON my_table.column2 = otherserver.otherdatabase.dbo.othertable.column2
This throws the following error:
Cannot bind multiparty ID "otherserver.otherdatabase.dbo.othertable.column1".
I never have problems if I only reference the table, but when I add the column name it always throws an error. Any ideas? SQL Server 2008
+3
eek142
source
to share
2 answers
Just use the table name when you qualify your columns.
UPDATE my_table
SET my_table.column1 = othertable.column1
FROM my_table INNER JOIN otherserver.otherdatabase.dbo.othertable
ON my_table.column2 = othertable.column2
Or use an alias.
UPDATE my_table
SET my_table.column1 = OT.column1
FROM my_table INNER JOIN otherserver.otherdatabase.dbo.othertable as OT
ON my_table.column2 = OT.column2
+5
Mikael Eriksson
source
to share
Use a table alias, then refer to the column:
UPDATE my_table
SET my_table.column1 = A.column1
FROM my_table INNER JOIN otherserver.otherdatabase.dbo.othertable A
ON my_table.column2 = A.column2
+1
Tomek
source
to share