Oracle / PLSQL: UPDATE Statement

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

Description

The Oracle UPDATE statement is used to update existing records in a table in an Oracle database. There are 2 syntaxes for an update query in Oracle depending on whether you are performing a traditional update or updating one table with data from another table.

Syntax

The syntax for the UPDATE statement when updating one table in Oracle/PLSQL is:

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

OR

The syntax for the Oracle UPDATE statement when updating one table with data from another table is:

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

Parameters or Arguments

column1, column2, ... column_n

The columns that you wish to update.

expression1, expression2, ... expression_n

The new values to assign to the column1, column2, ... column_n. 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.

Example - Update single column

Let's look at a very simple Oracle UPDATE query example.

UPDATE customers
SET last_name = 'James'
WHERE customer_id = 5000;

This Oracle UPDATE example would update the last_name to 'James' in the customers table where the customer_id is 5000.

Example - Update multiple columns

Let's look at an Oracle UPDATE example where you might want to update more than one column with a single UPDATE statement.

UPDATE customers
SET state = 'California',
    customer_rep = 32
WHERE customer_id > 100;

When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.

This Oracle UPDATE statement example would update the state to 'California' and the customer_rep to 32 where the customer_id is greater than 100.

Example - Update table with data from another table

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

UPDATE customers
SET c_details = (SELECT contract_date
                 FROM suppliers
                 WHERE suppliers.supplier_name = customers.customer_name)
WHERE customer_id < 1000;

This UPDATE example would update only the customers table for all records where the customer_id is less than 1000. When the supplier_name from the suppliers table matches the customer_name from the customers table, the contract_date from the suppliers table would be copied to the c_details field in the customers table.

Example - Using EXISTS Clause

You can also perform more complicated updates in Oracle.

You may wish to update records in one table based on values in another table. Since you can't list more than one table in the Oracle UPDATE statement, you can use the Oracle EXISTS clause.

For example:

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.customer_name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

In this Oracle UPDATE example, whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten to the customer_name from the customers table.

Practice Exercise #1:

Based on the suppliers table populated with the following data, update the city to "San Francisco" for all records whose supplier_name is "CISCO".

CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5001, 'GCP', 'Chicago');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5002, 'CISCO', 'Chicago');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5003, 'AWS', 'Detroit');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5004, 'MICRO', 'New York');

Solution for Practice Exercise #1:

The following UPDATE statement would perform this update in Oracle.

UPDATE suppliers
SET city = 'San Francisco'
WHERE supplier_name = 'CISCO';

The suppliers table would now look like this:

Practice Exercise #2:

Based on the suppliers and customers table populated with the following data, update the city in the suppliers table with the city in the customers table when the supplier_name in the suppliers table matches the customer_name in the customers table.

CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5001, 'GCP', 'New York');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5002, 'CISCO', 'Chicago');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5003, 'AWS', 'Detroit');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5005, 'MICRO', 'LA');

CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7001, 'GCP', 'San Francisco');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7002, 'CISCO', 'Toronto');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7003, 'AWS', 'Newark');

Solution for Practice Exercise #2:

The following UPDATE statement would perform this update in Oracle.

UPDATE suppliers
SET city = (SELECT customers.city
            FROM customers
            WHERE customers.customer_name = suppliers.supplier_name)
WHERE EXISTS (SELECT customers.city
              FROM customers
              WHERE customers.customer_name = suppliers.supplier_name);

The suppliers table would now look like this: