How to use update & join in OPENQUERY

Question

I have several rows in a SQL Server 2008 R2 table in

ConfirmTable (ItemID, Barcode)

and more rows in DB2 header file

O1 (ItemId, barcode, ProductionUnit, OwnerName, Delivered, Qty)

and Detail file

O2 (ItemId, barcode, Address, ItemName, Delivered, TotalLines)

I want to update DELIVERED status in O1 and O2 files to 'YES' if the ItemID and Barcode exist in ConfirmTable. I am trying to use following query but it seems showing following error.

OLE DB provider "DB2OLEDB" for linked server "LINK_DB" returned message "Insufficient key column information for updating or refreshing."

Code:

UPDATE OQ 
SET OQ.Delivered='YES'
FROM OPENQUERY(LINK_DB, 'SELECT * 
                         FROM XXXXR.HLIB.O1 O1O 
                         LEFT JOIN XXXXR.HLIB.O2 O2O ON O1O.ItemID = O2O.ItemID 
                         WHERE O1O.Qty > 0') OQ 
INNER JOIN 
    ConfirmTable CT ON CT.Barcode = OQ.Barcode

I also tried the following format of update still it did not work for me.

http://www.experts-exchange.com/questions/28390846/OPENQUERY-in-sql-server-linked-server.html

Answer 1

It's an old post but I had to deal with it now.

Don't use join in OPENQUERY.

Instead, for example, do something like this:

UPDATE OQ 
SET OQ.Delivered='YES'
FROM OPENQUERY(LINK_DB, 'SELECT * FROM XXXXR.HLIB.O1') OQ
JOIN OPENQUERY(LINK_DB, 'SELECT * FROM XXXXR.HLIB.O2') QQ2 ON QQ.ItemID = QQ2.ItemID  
INNER JOIN ConfirmTable CT ON CT.Barcode = OQ.Barcode
WHERE QQ2.Qty > 0