Oracle update from Solution

In table update operations, in many cases, data other than the table to be updated must be referenced in the expression. Like SQL Server provides the update from clause, it can connect the table to be updated with other data sources. Although only one table can be updated, by connecting the table to be updated with other data sources, you can reference data other than the table to be updated in the update expression.
For example:

Update Table2
Set table2.colb = table2.colb + table1.colb
From Table2
Inner join Table1
On (table2.cola = table1.cola );

The actual update operation is performed on the table to be updated, rather than on the new result set formed by the from clause.

Oracle does not have the update from syntax. You can use the following two methods to implement the same function:
1: subquery update a set a. Name = (select B. name from B where B. ID = A. ID)
(1) Single Column
Update

Set a. Name = (select B. name from B where B. ID = A. ID)

Where a. ID in (select ID from B );

(2) multiple columns
Update order_rollup

Set (qty, price) = (select sum (qty), sum (price) from order_lines where customer_id = 'kohl ')

Where cust_id = 'kohl 'and order_period = to_date ('01-Oct-2000 ')

2: Using views
Update (select a. Name aname, B. Name bname from a, B where a. ID = B. ID)
Set aname = bname;

For example:

Update tablea
Set a. fieldforupdate = (select B. fieldsource from tableb B where a. keyfield = B. keyfield)
Where exists (select B. fieldsource from tableb B where a. keyfield = B. keyfield)

Note the following three points:
1. For a given a. keyfield value, the value of select B. fieldsource from tableb B where a. keyfield = B. keyfield can only be a unique value, not a multi-value.
2. In most cases, the where exists clause at the end is important. Otherwise, an error is returned.
3. Restrictions on view update:
If a view is connected to multiple tables, the user's ability to update view records is limited. The base table of the view cannot be updated unless update only involves one table and the View column contains the entire primary key of the updated table.