The UPDATE statement is used to update records in a table.
The UPDATE statement is used to update existing records in a table.
Notice the WHERE clause in the SQL UPDATE statement! The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated! |
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
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 |
Assume we wish to update the customer "Alfreds Futterkiste" with a new contact person and city.
We use the following SQL statement:
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 | Hamburg | 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 |
Be careful when updating records. If we had omitted the WHERE clause, in the example above, like this:
The "Customers" table would have looked like this:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 |
Alfreds Futterkiste | Alfred Schmidt | Obere Str. 57 | Hamburg | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Alfred Schmidt | Avda. de la Constitución 2222 | Hamburg | 05021 | Mexico |
3 | Antonio Moreno Taquería | Alfred Schmidt | Mataderos 2312 | Hamburg | 05023 | Mexico |
4 |
Around the Horn | Alfred Schmidt | 120 Hanover Sq. | Hamburg | WA1 1DP | UK |
5 | Berglunds snabbköp | Alfred Schmidt | Berguvsvägen 8 | Hamburg | S-958 22 | Sweden |