Update table using Openquery linked server

sql sql-server sql-server-2008 tsql openquery
user1088172 picture user1088172 · Feb 8, 2013 · Viewed 30.4k times · Source

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.

Answer

Ivan G picture Ivan G · Feb 8, 2013

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

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More
SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine: CREATE PROCEDURE [dbo].[Item_AddItem] @CustomerId uniqueidentifier, @Description nvarchar(100), @Type int, @Username nvarchar(100), AS BEGIN DECLARE @TopRelatedItemId uniqueidentifier; SET @TopRelatedItemId = ( SELECT top(1) RelatedItemId FROM …

Read More