The MySQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition; 

Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!


Demo Database

Below is a selection from the “Customers” table in the Northwind sample database:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1
Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
4
Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK

UPDATE Table

The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.

Example

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'
WHERE CustomerID = 1;

The selection from the “Customers” table will now look like this:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1
Alfreds FutterkisteAlfred SchmidtObere Str. 57Frankfurt12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
4
Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK


UPDATE Multiple Records

It is the WHERE clause that determines how many records will be updated.

The following SQL statement will update the PostalCode to 00000 for all records where country is “Mexico”:

Example

UPDATE Customers
SET PostalCode = 00000
WHERE Country = 'Mexico';

The selection from the “Customers” table will now look like this:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1
Alfreds FutterkisteAlfred SchmidtObere Str. 57Frankfurt12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.00000Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.00000Mexico
4
Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK

Update Warning!

Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

Example

UPDATE Customers
SET PostalCode = 00000;

The selection from the “Customers” table will now look like this:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1
Alfreds FutterkisteAlfred SchmidtObere Str. 57Frankfurt00000Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.00000Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.00000Mexico
4
Around the HornThomas Hardy120 Hanover Sq.London00000UK