Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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" –  Phil Mar 13 '12 at 11:24

1 Answer 1

up vote 2 down vote accepted

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');
share|improve this answer
1  
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 –  Phil Mar 13 '12 at 11:28
1  
Yes Phil, it can also be used without rounding with quote. I written that way because one quote was missing in the query. –  neeraj Mar 13 '12 at 11:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.