- Code: Select all
UPDATE [SERVER].[DB].[SCHEMA].[TABLE] SET
field1 = 'Some Value',
field2 = 'Another Value',
field3 = source.value
[SQL SERVER TABLE] AS source
key_field = source.key_field
In testing it works as long as [SQL SERVER TABLE] only contains 1 row. Any more rows and I get the following error:
Cannot get the data of the row from the OLE DB provider "PGNP" for linked server "bbi".
I would use an UPDATE OPENQUERY however I need to set values based on a local SQL Server table. I would rather not have to create a temporary destination table, insert the rows there and then do an update query via OPENQUERY or EXEC if possible.
Thanks for any input,