MySQL in a Nutshell, 2nd Edition

Changing Data

You can change data in a table using a few different methods. The most basic and perhaps the most common method is to use the UPDATE statement. With this statement, you can change data for all rows or for specific records based on a WHERE clause.

Looking back on the results displayed from an earlier query, we can see that Graham Greene’s book Brighton Rock has a copyright year of 1937. That’s not correct; it should be 1938. To change or update that bit of information, we would enter the following SQL statement:

UPDATE books
SET pub_year = '1938'
WHERE book_id = '2';
   
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

First, we state the name of the table that’s being updated. Next, we include the SET keyword with the column to change and its corresponding new value. If we wanted to change the values of more than one column, we would provide a comma-separated list of each column along with the equals sign operator and the new respective values. SET is given only once.

The preceding SQL statement has a WHERE clause limiting the rows that will be updated by specifying a condition the row must meet. In this case, our condition is for a specific value of a unique column, so only one row will be changed. The results of the query show that one row was affected, one row was matched, one row was changed, and there were no problems to generate warnings.

Sometimes inserting data into a table will cause a duplicate row to be created because a row for the data ...