Updating and deleting data

We already know quite a lot about inserting and retrieving data, but if applications could only do this, they would be quite static. Editing this data as we need is what makes an application dynamic and what gives to the user some value. In MySQL, and in most database systems, you have two commands to change data: UPDATE and DELETE. Let's discuss them in detail.

Updating data

When updating data in MySQL, the most important thing is to have a unique reference of the row that you want to update. For this, primary keys are very useful; however, if you have a table with no primary keys, which should not be the case most of the time, you can still update the rows based on other fields. Other than the reference, you will need the new value and, of course, the table name and field to update. Let's take a look at a very simple example:

mysql> UPDATE book SET price = 12.75 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

In this...