Update table with data from another table oRACLE

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.

https://www.techonthenet.com/oracle/update.php