Update to Openrowset SQL SERVER 2008

Question

I'm trying to do an UPDATE action to a remote table from a local query. Obviously i know it should be with an OPENROWSET but i don't know how to do it.

I know you can Insert to an OPENROWSET doing something like this:

 set @cadsql = convert(varchar(max),
  'insert into openrowset(''SQLOLEDB'','''+@cadcon+''','+@bd+'.ctoxtractor.lm_vehiculos) ' + char(13) + 
  '           (cuenta,corporativo,economico,ctatipotractor,sctatipotractor,ultcambio,ciausu,usuario,estatusoper) ' + char(13) + 
  'select 423,fv.vehic,fv.numeco,9802,0,getdate(),0,''ADMIN'',fv.estatusoper ' + char(13) + 
  'from ficvehic fv ' + char(13) + 
  'order by fv.vehic ')

 exec (@cadsql) 

Or that i can do a select like this:

SET @cadsql = 'SELECT * FROM OPENROWSET(''sqloledb'',''driver=sql server;server='+@server+';database='+ @database +';uid='+@user+';pwd='+@password+''',' +
'''SELECT Column1, column2,...  
   FROM table'')'

INSERT INTO another_table
exec (@cadsql)

But i havn't come up with a solution for updating. Is that possible?

Thanks in advance!

Answer 1

If I understand your intention correctly, this should get you going:

UPDATE target
SET
  target.column = query.value,
  ...
FROM OPENROWSET(
  'provider',
  'connection string',
  'SELECT columns FROM yourtable'
) AS target
INNER JOIN (
  your local query
) AS query
ON
  target.column = query.column
  AND ...
;

Basically, this is same as you would go about updating a local table from a query, just using OPENROWSET instead of a table name in the FROM clause and specifying its alias in the UPDATE clause.

Of course, it is also important that the remote query should be updatable (e.g. it should return results from a single table), same as with its being insertable when you are using OPENROWSET to insert.