Curriculum
Course: SQL
Login

Curriculum

SQL

SQL References

0/80

MySQL Functions

0/139

SQL Server Functions

0/84

SQL Quick Ref

0/1
Text lesson

SQL UPDATE

The SQL 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: Caution should be exercised when updating records in a table. Pay attention to the WHERE clause in the UPDATE statement, which specifies which record(s) should be updated. Omitting the WHERE clause will result in all records in the table being updated.

Demo Database

Here is an excerpt from the Customers table that has been referenced in the examples:

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

5

Berglunds snabbköp

Christina Berglund

Berguvsvägen 8

Luleå

S-958 22

Sweden

UPDATE Table

The subsequent 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:

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

5

Berglunds snabbköp

Christina Berglund

Berguvsvägen 8

Luleå

S-958 22

Sweden

UPDATE Multiple Records

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

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

Example

UPDATE Customers
SET ContactName=‘Juan’
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

Juan

Avda. de la Constitución 2222

México D.F.

05021

Mexico

3

Antonio Moreno Taquería

Juan

Mataderos 2312

México D.F.

05023

Mexico

4

Around the Horn

Thomas Hardy

120 Hanover Sq.

London

WA1 1DP

UK

5

Berglunds snabbköp

Christina Berglund

Berguvsvägen 8

Luleå

S-958 22

Sweden

Update Warning!

Exercise caution when updating records. If you omit the WHERE clause, you will update ALL records!

Example

UPDATE Customers
SET ContactName=‘Juan’;

The excerpt from the “Customers” table will now appear as follows:

 

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Juan

Obere Str. 57

Frankfurt

12209

Germany

2

Ana Trujillo Emparedados y helados

Juan

Avda. de la Constitución 2222

México D.F.

05021

Mexico

3

Antonio Moreno Taquería

Juan

Mataderos 2312

México D.F.

05023

Mexico

4

Around the Horn

Juan

120 Hanover Sq.

London

WA1 1DP

UK

5

Berglunds snabbköp

Juan

Berguvsvägen 8

Luleå

S-958 22

Sweden