CODEWITHSUNDEEP
mysqlsql-serverexecutelinked-server
Cenderze
Cenderze

Reputation: 1212

Getting error using EXECUTE('UPDATE..') on a MySQL server from SQL Server

I intend to do:

EXECUTE('UPDATE tableA SET campaignkey = ''20170101'' where storekey = 16
 and campaignkey LIKE ''%,%''') at MYLINKEDSERVER

but I get the error that:

You have an error in your SQL syntax; [...] for the right syntax to use near 'where storekey = 16 and campaignkey LIKE '%,%''

Does anyone have any idea what is wrong? To me it seems like I might have one ' too many on my LIKE statement, but I have Always used '' to indicate a non-numeric value. I don't want to fiddle with this to prevent updating far too many values on this server.

campaignkey is non-numeric (I Believe varchar) and storekey is integer.

Edit

I must not use OPENQUERY() because it is not set up correctly, and this is a urgent update.

Edit 2

Seems like it is because of apostrophes 's in the EXECUTE statement.

When I conduct:

select * from openquery(linkedserver,'Select * from tableA where storekey = 16 
and campaignkey = ''20170826,151''')

it works, but when using:

EXECUTE('Select * from tableA where storekey = 16 
and campaignkey = ''20170826,151''') at linkedserver

I get the error that I need to check the manual by the where clause. From googling it appears however that the correct syntax in fact is:

EXECUTE('UPDATE TableX SET StringVar = ''stringValue'' WHERE intVar = 3 
AND stringVar = ''xxxxx''') AT LinkedServer

I don't know why this won't work for me.. I have tried many combinations of '', '" etc.

Upvotes: 0

Views: 63

Answers (1)

sepupic
sepupic

Reputation: 8687

What about this one? 

update openquery(linkedserver,'Select * from tableA where storekey = 16 
and campaignkey = ''20170826,151''')
set campaignkey = '20170101'

Upvotes: 1

Related Questions