How to update a table in a linked server query from a local result set?

Question

I'm running a stored procedure on a local sql server instance.

The SQL server instance is linked to a DB2 instance on our i-series.

I have a result set which is generated using an OPENQUERY statement joined on to a local table. It is just a list of IDs.

How do I now run an update, using OPENQUERY, with a

    WHERE id IN (idListFromPreviousQuery)

type statement.

Ideally, I'm looking for a set based solution, not cursor / loop based, I just really can't get my head around how to go about this.

Answer 1

You could use WITH or temp_table:

SELECT id
INTO #temptable
FROM ...;

UPDATE linked_server.db_name.schema_name.table_name
SET col_name = ...
WHERE id IN (SELECT id FROM #temptable);

or:

WITH cte(id) AS (
 SELECT id
 FROM ...
)
UPDATE linked_server.db_name.schema_name.table_name
SET col_name = ...
WHERE id IN (SELECT id FROM cte);

EDIT:

WITH cte(id) AS (
     SELECT id
     FROM ...
    )
UPDATE oq 
SET    col_name = ...
FROM   OPENQUERY([LINKEDSERVER],    'select ... from ...') oq 
WHERE oq.id IN (SELECT id FROM cte);