MySQL Primer 
by Daniel Bartholomew

Intro | Logging In | Viewing | Inserting | Modifying | Links

<-- Previous

Modifying MySQL Data

Next -->

On the previous page, we found an error in one of the entries in the Books table. Specifically, the word "Hobbit" was spelled "Hobbbit". Here is what the select statement told us:

mysql> select * from Books;
+---------+----------------------------+----------+----------+
| Book_ID | Title                      | SeriesID | AuthorID |
+---------+----------------------------+----------+----------+
|       1 | The Fellowship of the Ring |        1 |        1 |
|       2 | The Two Towers             |        1 |        1 |
|       3 | The Return of the King     |        1 |        1 |
|       4 | The Sum of All Men         |        2 |        2 |
|       5 | Brotherhood of the Wolf    |        2 |        2 |
|       6 | Wizardborn                 |        2 |        2 |
|       7 | The Hobbbit                |        0 |        1 |
|       8 | Lair of Bones              |        2 |        2 |
+---------+----------------------------+----------+----------+
8 rows in set (0.23 sec)

mysql>

The easiest way to fix this is by executing an update statement:

update Books set Title = "The Hobbit" where Book_ID = 7;

What this statement does is tell the database to update the Books table by setting the Title field to "The Hobbit" if the Book_ID equals 7. Executing this statement will give us something that looks like this:

mysql> update Books set Title = "The Hobbit" where Book_ID = 7;
Query OK, 1 row affected (1.13 sec)
Rows matched: 1	Changed: 1	Warnings: 0

mysql>

Notice the feedback that mysql gives you after successfully executing the update statement. It lets you know that one row in the database matched your statement (there's only one row with a Book_ID of 7), that it changed one row of the database (the one it matched), and that there were no warnings or problems that it could see with what you just did.

We can now check to see that our change has been made by executing another select statement:

mysql> select * from Books;
+---------+----------------------------+----------+----------+
| Book_ID | Title                      | SeriesID | AuthorID |
+---------+----------------------------+----------+----------+
|       1 | The Fellowship of the Ring |        1 |        1 |
|       2 | The Two Towers             |        1 |        1 |
|       3 | The Return of the King     |        1 |        1 |
|       4 | The Sum of All Men         |        2 |        2 |
|       5 | Brotherhood of the Wolf    |        2 |        2 |
|       6 | Wizardborn                 |        2 |        2 |
|       7 | The Hobbit                 |        0 |        1 |
|       8 | Lair of Bones              |        2 |        2 |
+---------+----------------------------+----------+----------+
8 rows in set (0.30 sec)

mysql>

Sure enough, "Hobbit" is now spelled correctly.

That's about it for this 10 minute primer on using the MySQL command-line client. Go to the next page for links to other MySQL resources.

Oh, and if you have some suggestions for making this primer better, contact the author: daniel@plumcreek.us

<-- Previous

Next -->

Creative Commons License
This work is licensed under a Creative Commons License.