CODEWITHSUNDEEP
linked-serverdb2-400
bmiguelh
bmiguelh

Reputation: 25

DELETE DB2 using OPENQUERY returns error about key column insufficient

I am trying to delete rows on DB2 i Series using a link server but am getting an error message. Key column information is insufficient or incorrect. Too many rows were affected by update

This is the query

DELETE FROM DB2
FROM OPENQUERY(TEST1, 'SELECT FIELD1 FROM LIBRARY1.FILE1') DB2
INNER JOIN #DLT_FILE1 DLT ON 
DB2.FIELD1 = DLT.FIELD1

There is one column in both temp file #DLT_FILE1 and DB2 table LIBRARY1.FILE1

Upvotes: 0

Views: 88

Answers (1)

Charles
Charles

Reputation: 23803

Db2 for IBM i (aka DB2-400) doesn't allow positioned deletes, ie from a cursor, that uses joins.

AMarc's suggestion might work, once you fix the syntax...I believe this is correct.

DELETE 
 FROM OPENQUERY(TEST1
               , 'SELECT FIELD1 FROM LIBRARY1.FILE1 DB2
                  WHERE EXISTS (SELECT 1 
                                FROM #DLT_FILE1 DLT 
                                WHERE DB2.FIELD1 = DLT.FIELD1)
                 ')

Upvotes: 2

Related Questions