How to update SQL table from other table when they are on different servers

Question

I've already run the following command to include another server instance.

EXEC sp_addlinkedserver @server='Server'

Now I'm trying to synchronize these databases using this:

UPDATE
    [Server].[ServerDB].[dbo].[tableName] 
SET 
    [Server].[ServerDB].[dbo].[tableName].[columnName] = [LocalDB].[dbo].[tableName].[columnName]
FROM 
    [Server].[ServerDB].[dbo].[tableName], [LocalDB].[dbo].[tableName]
WHERE 
    [Server].[ServerDB].[dbo].[tableName].id = [LocalDB].[dbo].[tableName].id  

This gave me the following error:

The objects "LocalDB.dbo.tableName" and "Server.ServerDB.dbo.tableName" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

So after fiddling around with it I tried various renditions of this:

UPDATE
    [Server].[ServerDB].[dbo].[tableName] 
SET 
    [Server].[ServerDB].[dbo].[tableName].[columnName] = [LocalDB].[dbo].[tableName].[columnName]
FROM 
    [Server].[ServerDB].[dbo].[tableName] serverDB
INNER JOIN
     [LocalDB].[dbo].[tableName] localDB
ON 
     serverDB.id = localDB.id 

But they all give me some sort of rendition of the error:

The multi-part identifier "Server.ServerDB.dbo.tableName.allrows" could not be bound.

Can someone spot what I'm missing?

Answer 1

You need to use this syntax when updating with a join:

UPDATE s
SET s.[columnName] = l.[columnName]
FROM 
    [Server].[ServerDB].[dbo].[tableName] s
INNER JOIN
     [LocalDB].[dbo].[tableName] l
ON 
     l.id = s.id