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

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' 

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.