How to update via Linked server without using Join / relationship?

Question

I need to use data from more than one table in the same select without having to JOIN / relationship (I want to set a value from one table to another through a Linked server).

I tried the following without success, because there is no relationship between the tables:

UPDATE tabelaA 
SET tabelaA.campoA = tabelaB.campoB 
FROM OPENQUERY([LINKEDSERVER], 'SELECT campoX, campoA FROM [baseA].[schema].[tabelaA]') 
INNER JOIN [baseB].[dbo].[tabelaB] ON tabelaA.Id = tabelaB.Id
WHERE tabelaA.Id = xxxx AND tabelaB.Id = aaaa AND tabelaB.outrocampo = 'Block'

Someone would have an example of how to do this in SQL Server?

Answer 1

When using server linking it is necessary to be aware to reduce traffic between servers. In the sql code you posted the OPENQUERY function is used and in this case I suggest you implement the filter in the sql code that is sent to the remote server:

-- código #1
UPDATE A 
  SET campoA = B.campoB 
  FROM OPENQUERY([LINKEDSERVER], 
                 'SELECT Id, campoX, campoA FROM [baseA].[schema].[tabelaA] where Id=xxxx') as A
       INNER JOIN [baseB].[dbo].[tabelaB] as B ON A.Id = B.Id
  WHERE B.Id = aaaa AND B.outrocampo = 'Block';

As above the remote server sends to the local server only the lines that meet the filter (WHERE clause), which reduces data traffic on the network.

---

There is the option to use 4 part identifier, where the sql code would be changed to

-- código #2
UPDATE A 
  set campoA = B.campoB 
  from [LINKEDSERVER].[baseA].[schema].[tabelaA] as A 
       inner join [baseB].[dbo].[tabelaB] as B ON A.Id = B.Id
  where A.Id = xxxx 
        and B.Id = aaaa 
        and B.outrocampo = 'Block';

In the article Programming and optimization of distributed queries you find the necessary care for when scheduling distributed consultations. Stay tuned for distribution statistics, as per the document "Distribution statistics requirements for OLE DB providers”.

Special attention in defining security in the linkage between instances.

---

Considering that there is no relationship between columns, and that a single row of table B is selected through filter in clause WHERE, then it can be simplified to:

-- código #3
declare @campoB xxx;
SELECT @campoB= B.campoB
  from [baseB].[dbo].[tabelaB] as B
  where B.Id = aaaa 
        and B.outrocampo = 'Block';

UPDATE A 
  set campoA = @campoB 
  from [LINKEDSERVER].[baseA].[schema].[tabelaA] as A
  where A.Id = xxxx;

Or else:

-- código #2 v2
UPDATE A 
  set campoA = B.campoB 
  from [LINKEDSERVER].[baseA].[schema].[tabelaA] as A 
       cross join [baseB].[dbo].[tabelaB] as B
  where A.Id = xxxx 
        and B.Id = aaaa 
        and B.outrocampo = 'Block';