How to edit a batch of entries in a MySQL database using an update query!

This morning I uploaded some sample information to my logging in database at work.  Due to hidden differences in character encoding from the spreadsheet I was copying from I ended up with some extra characters in my database field; this made it more difficult to read the sample numbers.

It looked something like this:

“OSU+AF8-13308+AF8-1”

When it should have looked like this:

“OSU-13308-1”

If I didn’t know about update queries I would have had to edit each of the 31 entries individually.

Luckily there is a much easier way.  Use an update query.

N.B. For this tutorial i’m going to assume that you can at least sign into the phpmyadmin pages of your database.  If you don’t know what that is, then this tutorial is too advanced for you.

In the phpmyadmin pages of my website is a helpful box for inputting what is known as an “SQL String”.  This is simply a set of commands in SQL language that tells the database what to do with it’s contents.

Step-1 Select your database:

Select your database on the left hand side of the phpmyadmin screen.  In our example it’s called “newdb”.

mysql_tut_1

 

 

 

 

 

Step-2 Select the SQL tab:

Select the SQL tab along the top menu of the main body of phpMyAdmin.mysql_tut_2

 

 

 

 

 

 

 

Step-3 Type in the query string:

Type the following into the “Run SQL query/queries on database newdb:” box:

UPDATE table_name SET field_name = REPLACE(field_name, 'thing_you_want_changing','thing_you_want_it_changing_to') WHERE field_name LIKE '%+AF8-%';

N.B. substitute table_name, field_name, thing_you_want_changing, and thing_you_want_it_changing_to with what you need to.

Then press “go” in the bottom left of the SQL box.

If the query has worked, you should get a message along the lines of “31 rows affected. ( Query took 0.0220 sec )”.  After running this query for my example, all of the records I wanted correcting were corrected!

Hey presto, you now know how to use a simple update query in MySQL, making use of the “REPLACE” function and the logical operators “WHERE” and “LIKE” to boot.  This is just one example, there are many other things you can do with update queries including dozens of logical operators and functions; depending on what needs doing!