UPDATE OPENQUERY

We run lots of integration stuff between COBOL and SQLServer and we are starting to update records in COBOL which means we need a speedy way to do update statements using either OPENQUERY or the four part path to the linked server.

So, I can update a record using the four part path no problem but it's not the quickest...

UPDATE SITWEBDEV.SITWEBDEV..T_Changes_Webout
SET field = value
where etc.

Then I tried it with OPENQUERY...


UPDATE OPENQUERY([SITWEBDEV] , '
select Process
from T_Changes_Webout
where Id < 14
') 
SET Process = 0

... and I got the following error:


OLE DB provider "MSDASQL" for linked server "SITWEBDEV" returned message "Key column information is insufficient or incorrect. Too many rows were affected by update.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "SITWEBDEV" could not UPDATE table "[MSDASQL]". 


The solutions was that the main key of the table was not included in the inner select statement. Once I added this it ran and was much quicker too.



update OPENQUERY([SITWEBDEV] , '
select Id, Process
from T_Changes_Webout
where Id < 14
') 
set Process = 0

Hat tip for the solution to:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_25603503.html