Table Querying   «Prev  Next»

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.
The next lesson contains an important warning about using the UPDATE statement.