Replace WordPress database content using SQL statements

I recently change my blog location from the root domain to a subdomain. Following the guide from WordPress everything went swimmingly, except one thing.

On some of the posts I published, I included images, and although my uploads folder had been moved from the root to the subdomain, the content for the images still pointed to the root address. Image links inside the post content were like <img src="http://www.grapii.com/wp-contents/uploads/... />

This caused the problem when displaying the post on the new subdomain as the images were linked to the old domain, resulting in a broken image icon.

To fix this I needed to change all <img src=.. /> links from the old to the new. I could manually edit each post via the admin panel one by one, but that would take way too long, cumbersome, error-prone and most of all annoying.

Luckily all post content is held in WordPress database table called wp-posts, and from there you could query the content using simple SQL statements. I had to take that a step further though, as I need to basically execute a find and replace on a large scale, and using an update SQL statement, I could do just that.

UPDATE wp_posts SET post_content = REPLACE ( post_content, 'http://www.grapii.com', 'http://blog.grapii.com' );

This SQL statement updates the post_content field, where the content is held, obviously, then replaces that content with itself, and if it finds http://www.grapii.com within then replace with http://blog.grapii.com.

What would have manually taken me days/weeks todo, this little SQL code did it all for me in seconds.

The power of SQL.

2 comments

  1. Man, that’s amazing post! I save maybe days of editing posts. I used notepad++ and sql database in xml format – replace all old links with the new one and thaht’s it! You also need to change the root of your future updates in settings -> media if you will keep you media files on different (sub)domain.

    I did all this because if your media files are on different subdomain, your blog will load much faster.

    Thank you for help!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>