UPDATE linked server table

[rrichardet]

Is it possible to run an Update query using the OPENRECORDSET function?

Here is my SQL:

SELECT c.maxofpims_bacrr
FROM t_highrisk_summary_complete AS c INNER JOIN
OPENROWSET('MSDAORA','Edwina';'rrichardet';'rrichardet',
'update t_pimsdata
set riskrtg = c.maxofpims_bacrr
where gcinum = 510108137')
AS o
ON c.credit_taker_gci = o.gcinum

EDWINA is an ORACLE linked server. I need the query to update the ORACLE table based on a join to the SQLSERVER table. Thanks in advance for your help.

 

[bperry]

I see no one has responded yet, so I'll take a shot at it for you, although I am waaaaay over my head here. I'd be willing to bet that this particular syntax you are trying will not work. i.e. If you want to be updating "something", it almost certainly going to be in the format: UPDATE something..., (not Select....).


Here's a relevant section for the Update statemment from BOL:
-------------------
Syntax
UPDATE
{
table_name | view_name | rowset_function_limited
}
SET
column_name = { expression | DEFAULT | NULL | variable}
-----------------
It looks like you can feed the OpenRowSet into the SQL Update statement. So now the trouble is to figure out that exact syntax. This is an attempt, which will not work, but I think is along the lines you should be thinking. Perhaps you can try something like this and then re-post.... maybe someone will recognize what we are doing here and help out.

UPDATE OpenRowSet('MSDAORA',
'Edwina';'rrichardet';'rrichardet',
'SELECT something
FROM something
WHERE something') c

SET CompanyName = 'Some-New-Name'
from SomeTable
Inner Join AnotherTable
ON ...
WHERE CustomerID = 'SAVEA'
----------------------------
Like I said, I feel that's where we should be heading. Hopefully someone can take us there.

bperry