MySQL semicolon in update statement is causing syntax error

249
June 01, 2017, at 10:26 AM

I have a MySQL DB (5.7.18) with articles table. Each article has an abstract as an html string in abstract column. Due to a bug in the HTML editor, many of html tags are polluted with annoying style, like this:

<h1 style="color: rgb(0, 0, 0);">Wonderful Abstract</h1>

In order to fix this i am trying the following query:

UPDATE `article` SET `abstract`=REPLACE(`abstract`,' style="color: rgb(0, 0, 0);"','')

Unfortunately, a syntax error is shown:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' style="color: rgb(0, 0, 0))' at line 1

If the semicolon is removed it works fine:

UPDATE `article` SET `abstract`=REPLACE(`abstract`,' style="color: rgb(0, 0, 0)"','');

But it must be replaced in the string too...

I found a reference here saying to escape it:

UPDATE `article` SET `abstract`=REPLACE(`abstract`,' style="color: rgb(0, 0, 0)\;"','')

But the error message is still shown.

Another option described in the same origin is to add a semicolon to the end of the statement:

UPDATE `article` SET `abstract`=REPLACE(`abstract`,' style="color: rgb(0, 0, 0);"','');

And the combination of those two:

UPDATE `article` SET `abstract`=REPLACE(`abstract`,' style="color: rgb(0, 0, 0)\;"','');

The error message is still there.

Answer 1

I cannot replicate this behaviour...

http://rextester.com/BZAPY91725

Perhaps you're using a SPROC?

READ ALSO
Return table result in a join clause - mysql

Return table result in a join clause - mysql

I'm trying to pull up a record from my data tables into a one dataview combining my table emp and gndsaleI have different types where I can get my result which can be found in gndsale(e

217
having issues while connecting tomcat to mysql in a docker container?

having issues while connecting tomcat to mysql in a docker container?

I have 2 docker services created via docker-swarm

263
Why cant i add this foreign key constraint?

Why cant i add this foreign key constraint?

Im frustratedI cannot add the foreign key constraint for the table Test_info:

134
Xamarin - Set Up Azure Offline Sync in C#

Xamarin - Set Up Azure Offline Sync in C#

I have watched this tutorial on setting up offline sync for a Xamarin application using an Azure backendI have a MySQL table on my Azure portal and I now wish to add the back-end calls to sync my local SQLite database with the MySQL cloud database

322