MySQL UPDATE

The MySQL UPDATE Statement

The UPDATE statement is employed to alter existing records within a table.

UPDATE Syntax

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

Note: Exercise caution when updating records in a table! Pay attention to the WHERE clause in the UPDATE statement, as it specifies which record(s) should be modified. Omitting the WHERE clause will result in all records in the table being updated.

Demo Database

Here is an excerpt from the “Customers” table in the Northwind sample database:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds FutterKiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK

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 “Customers” table selection will now appear as follows:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds FutterKiste	Alfred Schmidt	Obere Str. 57	Frankfurt	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK

UPDATE Multiple Records

The number of records updated is determined by the WHERE clause.

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

Example

UPDATE Customers
SET PostalCode = 00000
WHERE Country = ‘Mexico’;

Here is how the selection from the “Customers” table will appear now:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds FutterKiste	Alfred Schmidt	Obere Str. 57	Frankfurt	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	00000	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	00000	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK

Update Warning!

Exercise caution when updating records. Omitting the WHERE clause will result in ALL records being updated!

Example

UPDATE Customers
SET PostalCode = 00000;

Here is how the selection from the “Customers” table will appear now:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds FutterKiste	Alfred Schmidt	Obere Str. 57	Frankfurt	00000	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	00000	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	00000	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	00000	UK