MySQL UPDATE to replace text in LONGTEXT field

Question

I have a WordPress table wp_posts and I'd like to run an UPDATE that will replace a url from my old domain to my new domain.

For example, let's say that one record in the field post_content has the following content:

This is my <a href="http://www.my-old-site.com/link/to/some/page">old web</a> site.
<img src="http://www.my-old-site.com/wp-content/upload/2012/02/my-image-file.jps />

I'd like that to become:

This is my <a href="http://www.my-new-site.com/link/to/some/page">old web</a> site.
<img src="http://www.my-new-site.com/wp-content/upload/2012/02/my-image-file.jps />

I've tried the following query

UPDATE wp_posts AS w` 
SET w.post_content = REPLACE(w.post_content, 'my-old-site.com', 'my-new-site.com');

but I don't get any result.

Any idea how to fix it?

• Philᵀᴹ: What do you mean by "don't get any result"? Is there an error message, or does the string just not get updated? I just tried your above test case on MySQL 5.1.61 & it worked fine. "Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0"
• Hartley Brody: Found this searching for "mysql longtext find and replace" and I'm also updating all old wordpress posts from an old URL to a new one. Small world.

Answer 1

There is an error in your query near

AS w`

Alias name "w" should instead be

`w`

Try this:

UPDATE wp_posts AS `w` 
SET w.post_content = REPLACE(w.post_content, 'my-old-site.com', 'my-new-site.com');

• Philᵀᴹ: You don't even need to use quotes round the alias. UPDATE wp_posts AS w SET w.post_content = REPLACE(w.post_content, 'my-old-site.com', 'my-new-site.com'); works fine
• neeraj: Yes Phil, it can also be used without rounding with quote. I written that way because one quote was missing in the query.