MySQL in a Nutshell

Changing Data

You can change data in a database using a few different methods. The most basic and perhaps the most common method is to use the UPDATE statement. With an UPDATE 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 Olympia Vernon’s book Logic has a copyright year of 2003. That’s not correct; it should read 2004. To change or update that bit of information, enter the following SQL statement:

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

First, name the table that’s being updated. Next, issue the SET keyword with the column to change and its corresponding new value. If we want 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 declared only once, by the way.

This statement has a WHERE clause in which we’re limiting the rows that will change 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 errors to generate warnings.

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