When you run an UPDATE statement through a SQL Server linked server, SQL Server pulls all matching rows from the remote server to the local instance for computation, then writes the changes back. This round-trip is why UPDATE operations are slow over a linked server, even when SELECT runs fast — SELECT lets the remote server filter and return only the result rows, so far less data travels over the network.
Solution
Run the UPDATE logic on the remote server rather than pulling data locally. Two approaches work:
Option 1: Encapsulate the UPDATE as a remote stored procedure
Create a stored procedure on the remote server that contains the UPDATE logic, then call it through the linked server using four-part name syntax.
-- Call the remote stored procedure via four-part name syntax
DECLARE @return_status INT;
EXEC @return_status = <linked_server_name>.<database_name>.<schema_name>.<procedure_name>
@param1 = <value1>;This ensures the UPDATE runs on the remote server. The stored procedure must already exist on the remote SQL Server instance.
For a list of stored procedures supported by ApsaraDB RDS for SQL Server, see Stored procedures.
Option 2: Use OPENQUERY for a pass-through UPDATE
OPENQUERY sends the query directly to the linked server for execution. The UPDATE runs entirely on the remote server, with no data transferred to the local instance.
UPDATE OPENQUERY (<linked_server_name>, 'SELECT <column> FROM <schema>.<table> WHERE <condition>')
SET <column> = <new_value>;| Placeholder | Description | Example |
|---|---|---|
|
Name of the linked server | RemoteSvr |
<schema>.<table> |
Remote table to update | dbo.orders |
<condition> |
Filter to identify target rows | id = 101 |
<new_value> |
Value to set | 'processed' |
For the full OPENQUERY syntax and additional examples, see OPENQUERY (Transact-SQL).
Applies to
ApsaraDB RDS for SQL Server