Updating Database Tables

Inserting a New Table Entry

For the examples we have been using in this tutorial, we are using an account called games in which there is a table called scores for keeping track of high scores. The table was initialized from a file, but now we are going to add a new player in interactive mode. We will use the MySQL command called INSERT INTO to select the table and operation, and then the command SET to specify the value of any variables that we wish to initialize. In order to add a new player called "Richard", we will use the following syntax:

mysql> INSERT INTO scores SET Name="Richard";

Query OK, 1 row affected (0.00 sec)

Let's see the current values in the table scores.

mysql> SELECT * FROM scores;

+---------+------+
| Name    | Num  |
+---------+------+
| Phyllis |  987 |
| Randy   | 1285 |
| Don     |  919 |
| Mark    |    0 |
| Mary    |  567 |
| Bob     |   23 |
| Pete    |  456 |
| Sally   |  333 |
| Richard | NULL |
+---------+------+
9 rows in set (0.00 sec)

It is important to note that if a variable is a "PRIMARY KEY" or is specified in the initial table creation as being something "NOT NULL", a value must be supplied at the time the entry is inserted. Notice that Richard does not have a score at this time, so his score is not 0 but NULL instead.

Updating Information

Since Richard does not have a score at this time, let's take a look at the syntax to change the information in a table. We will use the command UPDATE to identify the type of action and the table being used, and then the operation SET to assign a value to a variable as well as WHERE to establish the criteria for updating the record. The systax for that command would be:

mysql> UPDATE scores SET Num=0 WHERE Name="Richard";

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM scores WHERE Num=0;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Now Richard's score is also zero. Of course, we could have created Richard's entry and assigned the initial score to zero during the insert operation by doing the following command instead:

mysql> INSERT INTO scores SET Name="Richard", Num=0;

We can even change one of the user's names. Let's suppose that Mary actually should be called Marianne. We can change that entry for the name in the following way:

mysql> UPDATE scores SET Name="Marianne" WHERE Name="Mary";

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0 

+----------+------+
| Name     | Num  |
+----------+------+
| Phyllis  |  987 |
| Randy    | 1385 |
| Don      |  919 |
| Mark     |    0 |
| Marianne |  567 |
| Bob      |   23 |
| Pete     |  456 |
| Sally    |  333 |
| Richard  |  100 |
+----------+------+
9 rows in set (0.00 sec)

Now let's try a slightly more sophisticated update operation. Suppose we wish to give 100 Bonus points to the score of anyone whose name begins with an "R", such as in "Randy" and "Richard". We could update each row separately by replacing their scores with the appropriate values, but the following approach is a bit better. We will use the command LIKE which permits us to have match of some value such as the leading "R" in both names, and use the wildcard character "%" to match the rest. We will then allow MySQL to do the arithmetic by adding 100 points to the old value of Num for any of those that match. The syntax for that command is:

mysql> UPDATE scores SET Num=Num+100 WHERE Name LIKE "R%";

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0    

+----------+------+
| Name     | Num  |
+----------+------+
| Phyllis  |  987 |
| Randy    | 1385 |
| Don      |  919 |
| Mark     |    0 |
| Marianne |  567 |
| Bob      |   23 |
| Pete     |  456 |
| Sally    |  333 |
| Richard  |  100 |
+----------+------+
9 rows in set (0.01 sec)

Now both scores have been changed.

Deleting a Table Entry

Now that we can add entries to the table, it will be important to learn how to delete them, too. The command for removing something from a table is DELETE FROM to specify the action and table, and then WHERE to indicate the criteria for deletion. If we desire to delete Mark from the table, the command would be:

mysql> DELETE FROM scores WHERE Name="Mark";

Query OK, 1 row affected (0.00 sec)

Let's see the current values in the table scores.

+----------+------+
| Name     | Num  |
+----------+------+
| Phyllis  |  987 |
| Randy    | 1385 |
| Don      |  919 |
| Marianne |  567 |
| Bob      |   23 |
| Pete     |  456 |
| Sally    |  333 |
| Richard  |  100 |
+----------+------+
8 rows in set (0.00 sec)

If we add another user back to the table, MySQL apparently puts it in the empty slot it has because Mark has been deleted.

mysql> INSERT INTO scores SET Name="Marty", Num=0;

Query OK, 1 row affected (0.00 sec)    

+----------+------+
| Name     | Num  |
+----------+------+
| Phyllis  |  987 |
| Randy    | 1385 |
| Don      |  919 |
| Marty    |    0 |
| Marianne |  567 |
| Bob      |   23 |
| Pete     |  456 |
| Sally    |  333 |
| Richard  |  100 |
+----------+------+
9 rows in set (0.00 sec)

ModifyingTable Attributes

Occasionally, it becomes necessary to change the attributes of one of the variables or columns in a table. This is a frequent situation for a variable that might be declared VARCHAR(20) lets say, and then the user wants to add something that might be 25 characters in length. Rather than destroying the entire table and starting from scratch, this modification can be done using the MySQL command, ALTER TABLE combined with the MODIFY command.

Before we modify a column or a field entry, lets take a look at how the fields are currently defined using the SHOW command:

mysql> SHOW FIELDS FROM scores;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Name  | varchar(20) | YES  |     | NULL    |       |
| Num   | int(5)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Now to change the Name variable from 20 to 25 characters, the command would be:

mysql> ALTER TABLE scores MODIFY Name VARCHAR(25);

Query OK, 9 rows affected (0.02 sec)
Records: 9  Duplicates: 0  Warnings: 0

Let's see how the values have changed:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Name  | varchar(25) | YES  |     | NULL    |       |
| Num   | int(5)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

There are very sophisticated queries and updates that can be done with MySQL. It is possible to add new columns to existing tables and even merge two databases into one large table. Please check out the documentation at the MySQL website for more information.

MySQL: www.mysql.com