MySQL UPDATE Statement

The MySQL UPDATE Statement

The UPDATE statement is used to make changes to existing rows. Always pair it with a WHERE clause unless you truly want to modify all rows.

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

Here is our Customers table:

CustomerID	Customer Name	Address	City	Postal Code	Country
1	Alfreds	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Christina Berglund	Berguvsvägen 8	Lulea	S-958 22	Sweden

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

UPDATE Customers
SET City = 'Munich'
WHERE CustomerID = 1;

After this change, the selection looks like:

CustomerID	Customer Name	Address	City	Postal Code	Country
1	Alfreds	Obere Str. 57	Munich	12209	Germany
2	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Christina Berglund	Berguvsvägen 8	Lulea	S-958 22	Sweden

Set a default postal code for all customers in Mexico:

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

After this update, the selection looks like:

CustomerID	Customer Name	Address	City	Postal Code	Country
1	Alfreds	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	00000	Mexico
3	Antonio Moreno	Mataderos 2312	México D.F.	00000	Mexico
4	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Christina Berglund	Berguvsvägen 8	Lulea	S-958 22	Sweden

If you omit the WHERE clause, every row will be updated. For example:

UPDATE Customers 
SET Country = 'USA';

This statement changes the Country for all customers to USA. Always double-check your WHERE clause. A safe workflow is: