Search code examples
sql-serverjoinnaming

Reference column in a table on another server in an UPDATE/JOIN (SQL Server)

I'm familiar with 4-part naming, but I get an error every time I try to reference a 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:

The multi-part identifier "otherserver.otherdatabase.dbo.othertable.column1" could not be bound.

I never have trouble if I am only reference a table, but when I append the column name, it always throws an error. Any ideas? SQL Server 2008

Solution

  • 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