How can I replace HTML in MySQL?

Question

I want to find and replace (or more accurately, append) some HTML in one of my MySQL database fields, where the field contains a specific string of text.

I can successfully find the relevant records using this query:

SELECT *
FROM `wp_posts`
WHERE `post_content` LIKE '%some phrase%'

This query returns all the records I want to append HTML to.

So I try the following query to append the HTML I want to use:

SELECT *
FROM `wp_posts`
WHERE `post_content` LIKE '%some phrase%'
UPDATE wp_posts SET post_content = REPLACE(post_content, '</strong></a>', '</strong></a>
<strong>Some additional piece of text</strong></p>')

But an error is returned:

#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 'UPDATE wp_posts SET post_content = INSERT(post_content, '&lt;/strong&gt;&lt;/a&gt;', '&lt;/stro' at line 4

I presume it doesn't like how I've formatted my HTML, but how should it be formatted?

gred · Accepted Answer · 2011-08-01 12:50:35Z

4

you are trying to execute two different queries. My guess is that you need to execute the following one:

UPDATE wp_posts SET post_content = REPLACE(post_content, '</strong></a>', '</strong></a>
<strong>Some additional piece of text</strong></p>') 
WHERE `post_content` LIKE '%some phrase%'

answered Aug 1, 2011 at 12:50

gred

5374 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Over a year ago

Brilliant! Thanks - I see what I was trying to do there :-)

Haim Evgi · Accepted Answer · 2011-08-01 12:50:32Z

4

look on UPDATE Syntax , need to be like

UPDATE wp_posts
SET post_content = REPLACE(post_content, '</strong></a>', '</strong></a>
<strong>Some additional piece of text</strong></p>')
WHERE `post_content` LIKE '%some phrase%'

answered Aug 1, 2011 at 12:50

Haim Evgi

126k46 gold badges222 silver badges226 bronze badges

Comments

Eddie · Accepted Answer · 2011-08-01 12:51:41Z

0

Have you tried using the select as an inner query? (assuming

UPDATE wp_posts SET post_content = REPLACE(post_content, '</strong></a>', '</strong></a>
<strong>Some additional piece of text</strong></p>') 

WHERE id in (SELECT id
FROM `wp_posts`
WHERE `post_content` LIKE '%some phrase%')

answered Aug 1, 2011 at 12:51

Eddie

10.2k4 gold badges47 silver badges59 bronze badges

Comments

Dave Vogt · Accepted Answer · 2011-08-01 12:52:56Z

0

There's nothing wrong with the content you're trying to replace. However, the way I see it, you construct your update query the wrong way: You put a SELECT right before an UPDATE query, probably expecting the UPDATE would only touch the SELECTed rows.

I think what you want is this:

UPDATE wp_posts 
    SET post_content = REPLACE(
        post_content,
        '</strong></a>',
        '</strong></a><strong>Some additional piece of text</strong></p>'
    )
WHERE `post_content` LIKE '%some phrase%'

answered Aug 1, 2011 at 12:52

Dave Vogt

19.7k7 gold badges46 silver badges56 bronze badges

1 Comment

James Over a year ago

Thanks for all your replies, guys :-)

Marco Marsala · Accepted Answer · 2019-03-25 11:44:57Z

0

MySQL >= 5.5 provides XML functions to solve your issue. You can combine ExtractValue, UpdateXML and REPLACE functions.

Reference: https://dev.mysql.com/doc/refman/5.5/en/xml-functions.html

answered Mar 25, 2019 at 11:44

Marco Marsala

2,5015 gold badges27 silver badges42 bronze badges

Collectives™ on Stack Overflow

How can I replace HTML in MySQL?

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related