MySQL UPDATE

The MySQL UPDATE Statement

A table’s current records can be changed using the UPDATE statement.

UPDATE Syntax

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

Note: Use caution while making changes to a table’s records! Take note of the UPDATE statement’s WHERE clause. It is specified which record or records need to be modified in the WHERE clause. Every record in the table will be updated if you remove the WHERE clause!

Demo Database

Below is a selection 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 first customer (CustomerID = 1) is updated with a new contact person and city using the SQL query that follows.

Example

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

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

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

How many records are updated is determined by the WHERE clause.

The PostalCode for all entries with the nation set to “Mexico” will be updated to 00000 by running the following SQL statement:

Example

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

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

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!

When updating records, exercise caution. THE WHERE clause is optional, and it will update ALL entries!

Example

				
					UPDATE Customers
SET PostalCode = 00000;

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

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

MySQL UPDATE

The MySQL UPDATE Statement

UPDATE Syntax

Demo Database

UPDATE Table

Example

UPDATE Multiple Records

Example

Update Warning!

Example

Explore Topic

Top Tutorials

Top Tutorials

Top Tutorials

Top Tutorials