Avatar of smkkaleem
smkkaleem

asked on 

How to join local tables with a linked server table to update the linked server table?

I have two tables (Category & SubCategory tables) residing on my local SQL server and a third table on a SQL server linkedserver called GroupName. If I want to view the three tables, I can easily view them using the following query:

SELECT *
FROM dbo.Category a
join dbo.SubCategory b ON a.Category=b.Category
JOIN OPENQUERY(Mavrick, 'select [Group], ID, Name, Abrev, Price1 from GroupName) AS c ON b.[Group]=c.[Group]
WHERE a.Category='00'

This works perfectly OK. However, if I want to update the Group table after joining the same two tables above, I get an error. Following is the update query I am trying to use:

UPDATE OPENQUERY(Mavrick,
'SELECT *
FROM dbo.Category a
join dbo.SubCategory b ON a.Category=b.Category
JOIN OPENQUERY(Mavrick, ''select [Group], ID, Name, Abrev, Price1 from GroupName) AS c ON b.[Group]=c.[Group]
WHERE a.Category='' 00'''
)
SET Price1 = Price1 + 1

That does not work and gives the following error:

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC dBase Driver] Syntax error in FROM clause.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].


I tried another way as follows:

UPDATE  c
SET c.Price1 =  c.Price1 + 1
FROM dbo.Category a
join dbo.SubCategory b ON a.Category=b.Category
JOIN OPENQUERY(Mavrick, 'select [Group], ID, Name, Abrev, Price1 from GroupName) AS c
ON b.[Group]=c.[Group]
WHERE a.Category='00'

Again that does'nt work either and the following error returns:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'c'.

I am completely lost and will appreciate if someone can help me out where am I doing wrong. I need to figure this out ASAP

Thank you
Databases

Avatar of undefined
Last Comment
xenon_je

8/22/2022 - Mon