3.8. 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 instance, suppose that we want to run an SQL statement that inserts data on a few books into the books table and one of the books is already in the table. If we use INSERT, we'll end up with a duplicate row. To prevent this, we can use the REPLACE statement, which inserts new rows and replaces existing rows with new data. From MySQL's perspective, duplicates occur only when unique columns would contain the same value. Because the rec_id column is assigned automatically, it's unlikely that we would duplicate it, because we wouldn't tend to assign its value when adding records. What's unique about each book is its ISBN number, which is the bar-code number on the back of the book. To ensure that we do not have rows with the same ISBN number, we'll alter our books table again and change the isbn column to a UNIQUE column, a column that requires a unique value. This way we won't be able to enter data inadvertently on a book more than once.

ALTER TABLE books  CHANGE COLUMN isbn isbn VARCHAR(20) UNIQUE;

Now we're ready to begin inserting data for more books without worrying about duplicate rows for books with the same ISBN number. Here is an example in which we're attempting to add two more books by Olympia Vernon, one of which is already in the table:

REPLACE INTO books (title, author_id, isbn, genre, pub_year) VALUES('Eden','1000','0802117287','novel','2003'),       ('Hiro','1000','0802117289','novel','2004');

The syntax for the REPLACE statement is the same as the INSERT statement. Notice that we've added two rows here in one statement. This is the same syntax that you would use if you want to add more than one row using INSERT. Just list each row's data within parentheses and separate them by commas, as shown earlier. In this example, there is already a row for the book containing the ISBN number 0802117287 (i.e., Eden), so it will be replaced and not added. There isn't one for her new book Hiro, though, so it will be added.