fastread homefastrread library fastread menu

SQL : UPDATE Statement

Tutorial by:Sweta      Date: 2016-04-14 23:13:51

❰ Previous Next ❱

This SQL tutorial explains how to use the SQL UPDATE statement with syntax, examples, and practice exercises. Notice that there are 3 ways to write a SQL UPDATE statement

Description

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

Syntax

The syntax for the SQL UPDATE statement when updating a table 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 for yourself!

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         www.techonthenet.com

       5000  Smith      Jane        www.digminecraft.com

       6000  Ferguson   Samantha    www.bigactivities.com

       7000  Reynolds   Allen       www.checkyourmath.com

       8000  Anderson   Paige

       9000  Johnson    Derek       www.techonthenet.com

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

UPDATE customers

SET first_name = 'Judy'

WHERE customer_id = 8000;

And then 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         www.techonthenet.com

       5000  Smith      Jane        www.digminecraft.com

       6000  Ferguson   Samantha    www.bigactivities.com

       7000  Reynolds   Allen       www.checkyourmath.com

       8000  Anderson   Judy

       9000  Johnson    Derek       www.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

-----------  -------------  -------------

        100  Microsoft      Redmond

        200  RIM            Waterloo

        300  Oracle         Redwood City

        400  Google         Mountain View

        500  Intel          Santa Clara

        600  Samsung        Seoul

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

 

UPDATE suppliers

SET supplier_id = 150,

    supplier_name = 'Apple',

    city = 'Cupertino'

WHERE supplier_name = 'RIM';

And then select the data from the suppliers table again:

SELECT * FROM suppliers;

These are the results that you should see:

SUPPLIER_ID  SUPPLIER_NAME  CITY

-----------  -------------  -------------

        100  Microsoft      Redmond

        150  Apple          Cupertino

        300  Oracle         Redwood City

        400  Google         Mountain View

        500  Intel          Santa Clara

        600  Samsung        Seoul

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

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  QUANTITY

----------  ------------  --------

         1  Pear                25

         2  Banana               0

         3  Orange              18

         4  Apple               45

And a table called summary_data with the following data:

PRODUCT_ID  CURRENT_LEVELS

----------  --------------

         1              10

         2              10

         3              10

         4              10

         5              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_levels = (SELECT quantity

                 FROM products

                 WHERE products.product_id = summary_data.product_id)

WHERE EXISTS (SELECT quantity

                 FROM products

                 WHERE products.product_id = summary_data.product_id);

And then select the data from the summary_data table again:

SELECT * FROM summary_data;

These are the results that you should see:

PRODUCT_ID  CURRENT_LEVELS

----------  --------------

         1              25

         2               0

         3              18

         4              45

         5              10

This UPDATE example would update the current_levels field in the summary_data table with the quantity from the products table where the product_id values match.

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_levels field to NULL in the last row of the summary_data table (because the products table does not have a record where product_id is 5).

 

❰ Previous Next ❱