'Update SQL server local table column from linked server table

I have local and a linked server tables in SSMS

  • local [Arc].[dbo].[Record]
  • linked server SQLSERVR.[ArcReport].[dbo].[RecordRemote]

when I query

  • select * from [Arc].[dbo].[Record]
  • select * from SQLSERVR.[ArcReport].[dbo].[RecordRemote]
    It brings results.

But when I query

    UPDATE [Arc].[dbo].[Record]
    SET [Arc].[dbo].[Record].Column1= SQLSERVER.[ArcReport].[dbo].[RecordRemote].Column2 
    WHERE [Arc].[dbo].[Record].id = 16

or

UPDATE localR
  SET localR.Column1= remoteR.Column2 
  FROM [Arc].[dbo].[Record] AS localR
  INNER JOIN SQLSERVER.[ArcReport].[dbo].[RecordRemote].Column2 AS remoteR
  ON localR.id= [SQLSERVER].[ArcReport].[dbo].[RecordRemote].id

it says
The multi-part identifier "SQLSERVER.ArcReport.dbo.RecordRemote.Column2" could not be bound.

sqlsql-server


Solution 1:[1]

You have made a mistake in your SQL query (see 3rd image).

  1. You can not use a column in the JOIN only tables go there (see 1st image)
  2. Since you defined and alias for SQLSERVER.[ArcReport].[dbo].[RecordRemote] as remoteR you must use it to access its fields (see SQL Aliases for more info and 2nd image) enter image description here enter image description here

enter image description here

Solution 2:[2]

You need to perform a join so that SQL Server knows what row to update from.

UPDATE localR
  SET localR.Column1 = remoteR.Column2
  FROM Arc.dbo.[Record] AS localR
  INNER JOIN LinkedServerName.ArcReport.dbo.RecordRemote AS remoteR
  ON <some join condition between localR and remoteR>;

BTW your syntax wouldn't work locally, either. It essentially boils down to:

UPDATE dbo.T1
  SET C1 = dbo.T2.C2;

This is simply invalid syntax. You can't surprise add a table reference like that.

Solution 3:[3]

Your join syntax is wrong :

INNER JOIN SQLSERVER.[ArcReport].[dbo].[RecordRemote].Column2 AS remoteR <= column2 is not a table

When doing a join, you join 2 tables (not columns), with a join condition on these 2 tables :

That makes :

UPDATE localR
SET localR.Column1= remoteR.Column2 
FROM [Arc].[dbo].[Record] AS localR
INNER JOIN SQLSERVER.[ArcReport].[dbo].[RecordRemote] AS remoteR
ON localR.id= remoteR.id

Solution 4:[4]

In SQL server inner join is not necessary while updating a table from another table, where clause will do the trick. The simplify solution for your problem would be:

  UPDATE Record 
  SET Record.Column1= remoteR.Column2 
  FROM SQLSERVER.[ArcReport].[dbo].[RecordRemote] AS remoteR
  where Record.id= [SQLSERVER].[ArcReport].[dbo].[RecordRemote].id and Record.id = 16

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Max
Solution 2 Aaron Bertrand
Solution 3 Velu
Solution 4 Kazi Mohammad Ali Nur

Related Questions