Find and Replace Updated URLs in the WordPress Database via SQL Query

When moving from staging environments to production, or local to staging, a common hiccup is media URLs clinging to their old paths. Utilizing SQL queries to directly change the database is an easy way to break the bad habits that these URLs developed in their past life.

When would I use this?

  • Moving a WordPress site from localhost to the world wide web and you need to make sure that all assets in posts and pages are properly pathed.
  • Moving WordPress site from a development or staging environment to the production server and you need to make sure that all assets in posts and pages are linked to the updated URL.
  • Adding an SSL certificate, to ensure that all assets are served under HTTPS.

How do I do it?

First, access the database of the new WordPress install.

While on localhost, I prefer using HeidiSQL – a free, open-source SQL navigation tool. Elsewhere I find myself using phpMyAdmin or simply the command line. Regardless of your weapon of choice, find your way to the place you can enter SQL queries.

Next, execute the queries.

Change the URLs to match your use case.

UPDATE wp_posts SET post_content = replace(post_content, 'http://localhost/oldsite', '');
UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://localhost/oldsite','');

That’s it.