CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlopenquery
user1088172
user1088172

Reputation:

Update table using Openquery linked server

I've tried this code and still got the following error, perhaps anyone could help? 

UPDATE a 
SET    a.MMDWNO = '21'
FROM   OPENQUERY(NMIIFLIB,
       'select * from MVXCDTANSN.MITMAS WHERE MMITTY = ''25''') a

Error :

OLE DB provider "MSDASQL" for linked server "NMIIFLIB" returned message "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - MITMAS in MVXCDTANSN not valid for operation.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "NMIIFLIB" could not UPDATE table "[MSDASQL]".

The select statement works fine but when I try to update I always stuck with this.

Upvotes: 6

Views: 46300

Answers (2)

Roushan Kumar
Roushan Kumar

Reputation: 11

you must try this. Hope this will help you.

UPDATE OPENQUERY(firstlink, 'select * from job.dbo.student where id = ''3''') 
    SET name = 'sanjeev acharya'

Upvotes: -2

Ivan Golović
Ivan Golović

Reputation: 8832

If you're trying to update a table on linked server, try this syntax:

UPDATE OPENQUERY(NMIIFLIB, 'select * from MVXCDTANSN.MITMAS where MMITTY = ''25''')
SET MMDWNO = 21

Upvotes: 13

Related Questions