Update URLs in MySQL Databases with SQL Queries: A Guide

Managing a MySQL database often involves performing updates to ensure data consistency or adapt to new configurations. One common scenario is updating URLs across a database, such as when removing a port number from URLs. In this post, we’ll guide you through the process of using SQL queries to achieve this efficiently.

The Scenario

Updating a bulk URL: For example, I am using a WordPress server and have created all the content. At the time, my WordPress URL was “http://demo.j2dk.in:8090,” and now I want to promote it as my blog site, which is “https://techwave.j2dk.in.” Therefore, we need to update the WordPress URL in the settings.

After doing this, accessing WordPress through the new URL will break all the content because all the content URLs in the database still point to the old URL.

Instead of updating the URLs one by one, which could take more than a day depending on the content volume, this SQL command can fix that in a single run.

The SQL Code

Here’s the SQL query to update all occurrences of the old URL in your MySQL database:

SET SQL_SAFE_UPDATES = 0;

UPDATE `your_table_name`
SET `your_column_name` = REPLACE(`your_column_name`, 'techwave.j2dk.in:8090', 'techwave.j2dk.in')
WHERE `your_column_name` LIKE '%techwave.j2dk.in:8090%';

SET SQL_SAFE_UPDATES = 1; -- Turn safe updates back on

Breaking Down the Query

Steps to Execute the Query

Backup Your Database: Before running any SQL queries, create a backup of your database to avoid accidental data loss.
Access Your Database: Use a tool like MySQL Workbench, phpMyAdmin, or the command line to connect to your database.
Run the Query: Replace your_table_name and your_column_name with the actual table and column names in your database. Paste the SQL code into your SQL editor and execute it. Ensure that the changes have been applied correctly.
Verify the Changes: Check your data to confirm that the old URLs have been successfully replaced.

Why This Approach Works

The REPLACE function in MySQL is a powerful tool that allows you to find and replace text within a column efficiently. By targeting only rows with the old URL using the WHERE clause, you ensure the query runs quickly and avoids unnecessary updates.

Conclusion

Using SQL to update URLs in a MySQL database can save you hours of manual work. However, always proceed with caution: backup your database, carefully plan your queries, and verify the changes. With this query, you’ll be able to seamlessly transition from techwave.j2dk.in:8090 to techwave.j2dk.in in just a few steps.

Feel free to reach out if you have any questions or need assistance with database management!