Using UPDATE Statement in Oracle

This article is written about how to use the Oracle UPDATE assertion with syntax, examples, and exercise exercises.

Description

The Oracle UPDATE declaration is used to update present files in a desk in an Oracle database. There are 2 syntaxes for an update question in Oracle relying on whether or not you are performing a regular update or updating one desk with data from some other table.

Syntax

The syntax for the UPDATE declaration 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 assertion when updating one table with records 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 cost of expression1, column2 would be assigned the fee of expression2, and so on. WHERE conditions Optional. The conditions that need to be met for the replace to execute. If no stipulations are provided, then all records in the desk will be updated.

Example – Update single column

Let’s seem at a very easy Oracle UPDATE query example.

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

This Oracle UPDATE instance would replace the last_name to ‘Anderson’ in the clients desk the place the customer_id is 5000.

Example – Update multiple columns

Let’s look at an Oracle UPDATE example the place you would possibly desire to replace greater than one column with a single UPDATE statement.

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

When you wish to replace more than one columns, you can do this by means of separating the column/value pairs with commas.

This Oracle UPDATE assertion example would replace the nation to ‘California’ and the customer_rep to 32 the place the customer_id is greater than one hundred

Example – Update desk with statistics from some other desk

Let’s seem to be at an Oracle UPDATE instance that shows how to update a table with data from some other 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 replace solely the customers table for all files where the customer_id is less than a thousand When the supplier_name from the suppliers desk matches the customer_name from the customers table, the contract_date from the suppliers table would be copied to the c_details subject in the customers table.

Example – Using EXISTS Clause

You can also operate extra tricky updates in Oracle.

You may also want to replace data in one desk primarily based on values in another table. Since you can not list more than one desk 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 desk populated with the following data, update the metropolis to “San Francisco” for all archives whose supplier_name is “IBM”.

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, 'Microsoft', 'Chicago');

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

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

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

Solution for Practice Exercise #1:

The following UPDATE announcement would perform this replace in Oracle.

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

The suppliers table would now look like this:

SUPPLIER_ID SUPPLIER_NAME CITY 5001 Microsoft Chicago 5002 IBM San Francisco 5003 Red Hat Detroit 5004 NVIDIA New York

Practice Exercise #2:

Based on the suppliers and clients desk populated with the following data, replace the town in the suppliers desk with the metropolis in the customers table when the supplier_name in the suppliers desk matches the customer_name in the clients 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, 'Microsoft', 'New York');

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

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

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5005, 'NVIDIA', '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, 'Microsoft', 'San Francisco');

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

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

Solution for Practice Exercise #2:

The following UPDATE assertion would operate 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:

SUPPLIER_ID SUPPLIER_NAME CITY 5001 Microsoft San Francisco 5002 IBM Toronto 5003 Red Hat Newark 5004 NVIDIA LA