Oracle update from Solution

Source: Internet
Author: User

In many cases, you need to reference data other than the table to be updated 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 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.