| Lesson 10 | The UPDATE statement |
| Objective | Become familiar with the UPDATE statement. |
SQL UPDATE Statement
You have seen how to insert information in the table, and how to get it back out. The next logical step is to make changes to that information, perhaps changing a city on a customer's account or updating a zip code.
The UPDATE statement is what you will use to make these types of changes to the information.
The UPDATE statement tells the server what you want to change, where the pertinent information resides, and how to control what rows are updated with the change. The basic syntax of the UPDATE statement is
UPDATE what SET changes to make WHERE filter
So, if you want to update the City column in the customer table for the Sasser Lastname row,
the following statement would do the trick:
UPDATE customer SET city='Seattle'
WHERE lastname='Sasser'
This will update the row where the last name is Sasser, and it will set the city to Seattle.
You can confirm this by issuing a follow-up SELECT statement and reviewing the results.
You should have something similar to the figure below.
| CustomerID |
LastName |
FirstName |
Address1 |
Address2 |
City |
State |
Zip Code |
Phone |
| 1 |
Sasser |
Sheila |
9761 Vivian St |
Unit C |
Ann Arbor |
MI |
48180-3103 |
313-292-3778 |
| 2 |
Alexis |
Davis |
3015 W Villa Rita Dr |
Unit C |
Phoenix |
AZ |
85053 |
(480) 505-8877 |
You can also update more than one column for a row by placing a comma in between the different columns:
UPDATE customer SET Address='5678 S. Main Street',
City='San Francisco'
WHERE lastname='Sasser'
Updating Data
The next statement shows how these columns can be populated via an update statement:
mysql> UPDATE person
-> SET street = '1225 Tremont St.',
-> city = 'Boston',
-> state = 'MA',
-> country = 'USA',
-> postal_code = '02138'
-> WHERE person_id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
The server responded with a two-line message: the "Rows matched: 1" item tells you that the condition in the where clause matched a single row in the table, and the "Changed: 1" item tells you that a single row in the table has been modified. Since the where clause specifies the primary key of William's row, this is exactly what you would expect to have happen. Depending on the conditions in your where clause, it is also possible to modify more than one row using a single statement. Consider, for example, what would happen if your where clause
looked as follows:
WHERE person_id < 10
Since both William and Susan have a person_id value less than 10, both of their rows would be modified. If you leave off the where clause altogether, your update statement will modify every row in the table.
