In SQL Server, the OPENQUERY rowset function enables you to execute a pass-through query on a linked server.

OPENQUERY is commonly referenced in the FROM clause of a query as if it were a table, but it can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement.

This article presents an example of using OPENQUERY to do an UPDATE pass-through query.

Example

Suppose the linked server called Homer has a table in the Pets database called Cats with the following data.

SELECT * FROM OPENQUERY (
    Homer, 
    'SELECT * FROM Pets.dbo.Cats'
    );

Result:

+---------+-----------+
| CatId   | CatName   |
|---------+-----------|
| 1       | FluffDupp |
| 2       | Scratch   |
| 3       | Long Tail |
+---------+-----------+

And let’s say we want to update one of the cat’s names.

We could do that using an UPDATE pass-through query against the linked server, like this.

UPDATE OPENQUERY (
    Homer, 
    'SELECT CatName FROM Pets.dbo.Cats WHERE CatId = 3'
    )
SET CatName = 'Short Tail';

Result:

(1 row affected)

Check the Results

We can now run a SELECT pass-through query to see the results.

SELECT * FROM OPENQUERY (
    Homer, 
    'SELECT * FROM Pets.dbo.Cats'
    );

Result:

+---------+------------+
| CatId   | CatName    |
|---------+------------|
| 1       | FluffDupp  |
| 2       | Scratch    |
| 3       | Short Tail |
+---------+------------+

As expected, the third row has now been updated.

One thing I should point out is that OPENQUERY does not accept variables for its arguments.