How to Bulk Update Internal Links in WordPress

owl-mascot

I have recently merged the stosu.com site’s content into wphelp365.com. While doing this, I decided to change the structure of my blog’s permalinks. All my blog posts are now published under https://wphelp365.com/blog/. Because of that decision, I had to bulk update the internal links within all posts.

For example, in the post titled How to Decrease the Size of Your WordPress Database by 80%, there is a link leading to another post titled Remove Massive Amounts of Akismet Comment Metadata in WordPress. Originally, that link would lead to https://stosu.com/remove-massive-amounts-of-akismet-comment-metadata-in-wordpress/, but now I wanted it to lead to https://wphelp365.com/blog/remove-massive-amounts-of-akismet-comment-metadata-in-wordpress/.

There are two ways to bulk update internal links in WordPress — manually or programmatically.

What will you learn in this post?

I will show you:

  • how to download a list of all the internal links of your WordPress site
  • how to manipulate that list to create SQL queries
  • how to use those SQL queries to update your internal links in bulk
  • how to create a PHP script to update all your internal links without any manual work

The Manual Way

Of course, by manual I don’t mean editing each post, finding each internal link in them, and changing each internal link manually. That would be both stupid and take too long.

How about using a plugin to do this job?

There is a plugin called List URLs. What it does is very simple — it downloads most of the internal URLs present in your WordPress installation. These URLs will include your posts, pages, custom post types, archives, and taxonomy archives.

OK, let’s do this.

First, install and activate the List URLs plugin.

Next, go to WordPress Dashboard > Tools > List URLs.

Download the list of this site’s URLs

Once you click on the Download CSV button, you are presented with a popup window to save the CSV file to your hard drive.

Save the CSV file to your hard drive

Select Save File and click on the OK button.

The downloaded CSV file includes five columns: Page Title, URL, WordPress ID, Post Type, and Post Status. The first one will include your posts as well as your pages.

Now that you have your CSV file, you might as well deactivate or even remove the List URLs plugin, because this is the only feature it has got.

Next, open the CSV file in Libre Office or Open Office.

You will be presented with the Text Import popup. Here, you need to select the correct separator options, so that Libre Office or Open Office display the CSV file’s content in the intended way. Use comma as the text separator and ” (double quote) as the text delimiter.

Import the CSV file into Libre Office or Open Office

Select all your post URLs in column B and press Ctrl+C to store them in memory for now.

Select post URLs

Next, press Ctrl+A to select all the content in the current sheet and press Delete.

Now paste your saved selection into column D of the blank sheet using Ctrl+V.

Next, run a Find & Replace (Ctrl+H) on the data you have just inserted into the sheet to replace the old values with the new ones. Like this:

Find & Replace

As you can see, in my case, the old value was stosu.com and it was replaced by the new value which was wphelp365.com/blog.

Now paste your saved selection again, this time into column B using Ctrl+V.

Next, copy and paste the following lines of code. Line 1 into column A. Line 2 into column C. And line 3 into column E.

This is what you should be looking at in Libre Office or Open Office at this point:

List of SQL queries

You have basically stitched together a list of SQL queries for the purpose of bulk updating the URLs of all your posts.

Next, press Ctrl+* to select all the content on your data sheet and press Ctrl+C to copy that selection to memory.

Now launch Notepad++ and paste that selection in using Ctrl+V.

The SQL queries in Notepad++

You can close Libre Office or Open Office at this point as you won’t need them anymore.

Next, run a Find & Replace (Ctrl+H) in Notepad++ to remove the tab character from the SQL queries. You need to manually copy that tab character and paste it into the Replace popup window next to Find what. Leave the Replace with box empty.

Remove tab character from the SQL queries

After this, you will be looking at your final SQL queries to be used on your WordPress database.

The sanitized SQL queries in Notepad++

Finally, log in to your phpMyAdmin account. Select the database you wish to query. Click on the SQL menu item and copy/paste in all the SQL queries you have in Notepad++. Then click Go to run the queries.

Read the following post for a more detailed description of how to bulk update post content via phpMyAdmin:

Related Post

How to Easily Change Content in Multiple Posts


From time to time bloggers need to change content in multiple posts. Reasons for this may vary. Usually it is best not to do such changes manually.

And in a few seconds it’s all done!

The Coder’s Way

While the manual way described above works well, it’s also a very elaborate way of doing things. It’s much easier to achieve the same results with a PHP script.

Save the following script as a PHP file on your hard drive.

The Testing Phase

Replace https://stosu.com/ in line 21 with your actual site URL.

Update line 26, so that it reflects the exact changes you wish to implement on your posts’ internal links. You need to concatenate the correct string. If you are not sure what that means, you can read up on the topic in the PHP Manual.

Once you have updated line 26, uncomment line 28 and run the script. The print_r function will display the new URLs as they will be implemented.

If the results you see are not correct, you need to go back to line 26 and work on it further.

The Execution Phase

Once you achieved the desired results in the testing phase, you can now move on to the execution phase.

First, comment out line 28 again.

Next, replace your database connection values on lines 12 to 15 with your actual host, database name, user name, and password.

Remove lines 48 and 31 to activate the part of the script which will be updating your database.

Save the PHP file and upload it to your WordPress installation’s root folder. That’s where wp-config.php is located.

And, finally, run the script to execute the changes on your WordPress database.

Important Note

I only tested this code in my development environment on my computer, not on a production site. If you decide to customize this code and run it on a live site, make sure that your remove the PHP file from your server immediately after you have executed your SQL queries. Otherwise, your site will be at serious risk of hacking.

Final Thoughts

I hope that this tutorial covered all major areas of how to bulk update internal links in WordPress.

In case you have a similar but more complex problem facing you, feel free to reach out to me and I will help you out:

Related Service

Small Fix Job


WP Help 365 to the rescue! Order a Small Fix Job for your WordPress website for only $49. Instant response. Same day delivery.

Liked this post?

Subscribe to our newsletter to receive early notification of new posts and deals:

Next Post »« Previous Post

Leave a Reply

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