Basic about UPDATE Statement – using and example

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

Description

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

Subscribe

Syntax

The syntax for the UPDATE announcement when updating a desk in SQL is:

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

OR

The syntax for the SQL UPDATE assertion when updating a table with facts from some other 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 approved 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 want to update. expression1, expression2 These are the new values to assign to the column1, column2. So column1 would be assigned the fee of expression1, column2 would be assigned the cost of expression2, and so on. WHERE conditions Optional. The conditions that need to be met for the update to execute. If no prerequisites are provided, then all data in the desk will be updated.

DDL/DML for Examples

If you desire to observe alongside with this tutorial, get the DDL to create the tables and the DML to populate the data. Then attempt the examples in your personal database!

Get DDL/DML

Example – Update single column

Let’s seem at an instance showing how to use the SQL UPDATE announcement to replace a single column in a table.

In this UPDATE example, we have a desk 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 display how the UPDATE statement works by using 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 clients 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 subject is set to ‘Judy’ in the customers table the place the customer_id is equal to 8000.

Example – Update multiple columns

Let’s seem at an UPDATE example that shows how to replace extra than one column in a table.

TIP: When you update more than one columns in an UPDATE statement, you want 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 metropolis 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 exhibit how to use the UPDATE announcement to update more than one column cost 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 document updated. Select the facts 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 replace the supplier_id to 150, the supplier_name to ‘Apple’ and city to ‘Cupertino’ the place the supplier_name is ‘Google’.

Example – Update table with statistics from every other desk

Let’s appear at an UPDATE instance that suggests how to replace a desk with records from any other table.

In this UPDATE example, we have a desk known as 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 desk 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 desk with values from the merchandise 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 archives update. Select the information 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 instance would update the current_category area in the summary_data desk with the category_id from the merchandise desk where the product_id values match. The first 5 files in the summary_data table have been updated.

TIP: Notice that our UPDATE declaration protected an EXISTS situation in the WHERE clause to make positive that there used to be a matching product_id in each the products and summary_data desk earlier than updating the record. If we hadn’t covered the EXISTS condition, the UPDATE query would have up to date the current_category field to NULL in the 6th row of the summary_data desk (because the products desk does now not have a record where product_id=8).

Practice Exercises

If you favor to take a look at your skills using the SQL UPDATE statement, attempt some of our practice exercises.

These exercises enable you to strive out your competencies with the UPDATE statement. You will be given questions that you want to solve. After each exercise, we supply the answer so you can check your answer. Give it a try!

Go to Practice Exercises