totn SQL

SQL: UPDATE Statement

This SQL tutorial explains how to use the SQL UPDATE statement with syntax, examples and practice exercises.

Description

The SQL UPDATE statement is used to update existing records in the tables.

subscribe button Subscribe

Syntax

The syntax for the UPDATE statement when updating a table in SQL is:

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
[WHERE conditions];

OR

The syntax for the SQL UPDATE statement when updating a table with data from another table is:

UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
[WHERE conditions];

OR

The syntax for the SQL UPDATE statement when updating multiple tables (not permitted in Oracle) is:

UPDATE table1, table2, ...
SET column1 = expression1,
    column2 = expression2,
    ...
WHERE table1.column = table2.column
[AND conditions];

Parameters or Arguments

column1, column2
The columns that you wish to update.
expression1, expression2
These are the new values to assign to the column1, column2. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.
WHERE conditions
Optional. The conditions that must be met for the update to execute. If no conditions are provided, then all records in the table will be updated.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - Update single column

Let's look at an example showing how to use the SQL UPDATE statement to update a single column in a table.

In this UPDATE example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

Now let's demonstrate how the UPDATE statement works by updating one column in the customers table. Enter the following UPDATE statement:

Try It
UPDATE customers
SET first_name = 'Judy'
WHERE customer_id = 8000;

There will be 1 record updated. Select the data from the customers table again:

SELECT * FROM customers;

These are the results that you should see:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Judy NULL
9000 Johnson Derek techonthenet.com

In this UPDATE example, the first_name field is set to 'Judy' in the customers table where the customer_id is equal to 8000.

Example - Update multiple columns

Let's look at an UPDATE example that shows how to update more than one column in a table.

TIP: When you update multiple columns in an UPDATE statement, you need to comma separate the column/value pairs in the SET clause.

In this UPDATE example, we have a table called suppliers with the following data:

supplier_id supplier_name city state
100 Microsoft Redmond Washington
200 Google Mountain View California
300 Oracle Redwood City California
400 Kimberly-Clark Irving Texas
500 Tyson Foods Springdale Arkansas
600 SC Johnson Racine Wisconsin
700 Dole Food Company Westlake Village California
800 Flowers Foods Thomasville Georgia
900 Electronic Arts Redwood City California

Now let's demonstrate how to use the UPDATE statement to update more than one column value at once. Enter the following UPDATE statement:

Try It
UPDATE suppliers
SET supplier_id = 150,
    supplier_name = 'Apple',
    city = 'Cupertino'
WHERE supplier_name = 'Google';

There will be 1 record updated. Select the data from the suppliers table again:

SELECT * FROM suppliers;

These are the results that you should see:

supplier_id supplier_name city state
100 Microsoft Redmond Washington
150 Apple Cupertino California
300 Oracle Redwood City California
400 Kimberly-Clark Irving Texas
500 Tyson Foods Springdale Arkansas
600 SC Johnson Racine Wisconsin
700 Dole Food Company Westlake Village California
800 Flowers Foods Thomasville Georgia
900 Electronic Arts Redwood City California

This UPDATE example would update the supplier_id to 150, the supplier_name to 'Apple' and city to 'Cupertino' where the supplier_name is 'Google'.

Example - Update table with data from another table

Let's look at an UPDATE example that shows how to update a table with data from another table.

In this UPDATE example, we have a table called products with the following data:

product_id product_name category_id
1 Pear 50
2 Banana 50
3 Orange 50
4 Apple 50
5 Bread 75
6 Sliced Ham 25
7 Kleenex NULL

And a table called summary_data with the following data:

product_id current_category
1 10
2 10
3 10
4 10
5 10
8 10

Now let's update the summary_data table with values from the products table. Enter the following UPDATE statement:

UPDATE summary_data
SET current_category = (SELECT category_id
   FROM products
   WHERE products.product_id = summary_data.product_id)
WHERE EXISTS (SELECT category_id
   FROM products
   WHERE products.product_id = summary_data.product_id);

There will be 5 records update. Select the data from the summary_data table again:

SELECT * FROM summary_data;

These are the results that you should see:

product_id current_category
1 50
2 50
3 50
4 50
5 75
8 10

This example would update the current_category field in the summary_data table with the category_id from the products table where the product_id values match. The first 5 records in the summary_data table have been updated.

TIP: Notice that our UPDATE statement included an EXISTS condition in the WHERE clause to make sure that there was a matching product_id in both the products and summary_data table before updating the record.

If we hadn't included the EXISTS condition, the UPDATE query would have updated the current_category field to NULL in the 6th row of the summary_data table (because the products table does not have a record where product_id=8).

Practice Exercises

If you want to test your skills using the SQL UPDATE statement, try some of our practice exercises.

These exercises allow you to try out your skills with the UPDATE statement. You will be given questions that you need to solve. After each exercise, we provide the solution so you can check your answer. Give it a try!

Go to Practice Exercises