The UPDATE statement is used to update records in a table.
The SQL UPDATE Statement
The UPDATE statement is used to update existing records in a table.
SQL UPDATE Syntax
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
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!
Demo Database
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 Constitucion 2222 | Mexico D.F. | 05021 | Mexico |
3 | Antonio Moreno Taqueria | Antonio Moreno | Mataderos 2312 | Mexico D.F. | 05023 | Mexico |
4 |
Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbkop | Christina Berglund | Berguvsvagen 8 | Lulea | S-958 22 | Sweden |
SQL UPDATE Example
Assume we wish to update the customer "Alfreds Futterkiste" with a new contact person and city.
We use the following SQL statement:
Example
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';
Try it Yourself »
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 Constitucion 2222 | Mexico D.F. | 05021 | Mexico |
3 | Antonio Moreno Taqueria | Antonio Moreno | Mataderos 2312 | Mexico D.F. | 05023 | Mexico |
4 |
Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbkop | Christina Berglund | Berguvsvagen 8 | Lulea | S-958 22 | Sweden |
Update Warning!
Be careful when updating records. If we had omitted the WHERE clause, in the example above, like this:
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg';
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 Constitucion 2222 | Hamburg | 05021 | Mexico |
3 | Antonio Moreno Taqueria | Alfred Schmidt | Mataderos 2312 | Hamburg | 05023 | Mexico |
4 |
Around the Horn | Alfred Schmidt | 120 Hanover Sq. | Hamburg | WA1 1DP | UK |
5 | Berglunds snabbkop | Alfred Schmidt | Berguvsvagen 8 | Hamburg | S-958 22 | Sweden |