SQL UPDATE statement

The UPDATE statement is used to update records that already exist in the table.

You can also use the AND or OR operator to combine multiple conditions.

SQL UPDATE syntax

The basic syntax for UPDATE queries with WHERE clauses is as follows: 

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

SQL UPDATE statement (update the records in the table) Please note
Be careful when updating records in the table!
Pay attention to the WHERE clause in the SQL UPDATE statement!
The WHERE clause specifies which records need to be updated. If the WHERE clause is omitted, all records will be updated!

Demonstrate the database

In this tutorial, we'll use the famous Northwind sample database.

Here's the data 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

SQL UPDATE instance

The following SQL statement updates "CustomerName" and "City" for the first customer (CustomerID s 1):

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

Now, the data selected from the "Customers" table looks 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 WHERE clause determines the number of records to be updated.

The following SQL statement updates the contact name of all records in the country as "Mexico" to "Juan": 

UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';

The selection in the "Customers" table now looks 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 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!

Be careful when updating records. I f you omit the WHERE clause, all records will be updated! 

UPDATE Customers
SET ContactName='Juan';


The "Customers" table will look like this:

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