Solution to UPDATEFROM in Oracle

In many cases, you need to reference data other than the table to be updated in the expression. Like sqlserver, which provides the update from clause, you can update the table

In many cases, you need to reference data other than the table to be updated in the expression. Like SQL server provides the from clause for update, you can update the table

In table update operations, data other than the table to be updated must be referenced in expressions in many cases. 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 dbo. Table2

SET dbo. Table2.ColB = dbo. Table2.ColB + dbo. Table1.ColB

FROM dbo. Table2

Inner join dbo. Table1

ON (dbo. Table2.ColA = dbo. 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 a 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 four 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.

4. If a ORA-01779 error occurs because of ORACLE restrictions, the solution reference ORA-01779 error handling method.