Search results
If you want to update specific rows (ie where the IDs match) you probably want to do a coordinated subquery. However, since you are using Oracle, it might be easier to create a materialized view for your query table and let Oracle's transaction mechanism handle the details.
6 gru 2017 · Using a subquery in an UPDATE statement can be a good way to improve the maintainability of your queries. It can also reduce the number of steps required to update your data by compressing two or more queries into a single query.
Subquery Method. The first option is to do an update of the DEST_TAB table using a subquery to pull the correct data from the SOURCE_TAB table. Notice the EXISTS predicate to exclude rows from the DEST_TAB table with no matching row in the SOURCE_TAB table.
A subquery is a SELECTstatement nested inside another statement such as SELECT, INSERT, UPDATE, or DELETE. Typically, you can use a subquery anywhere that you use an expression. Consider this following subquery example that uses the productstable from the sample database.
Use the UPDATE statement to change existing values in a table or in the base table of a view or the master table of a materialized view. Prerequisites. For you to update values in a table, the table must be in your own schema or you must have the UPDATE object privilege on the table. For you to update values in the base table of a view:
You may use subqueries in place of tables in this way as well in INSERT, UPDATE, and DELETE statements. Subqueries so used can employ correlation variables, both defined within the subquery itself and those defined in query blocks containing the subquery.
The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table.
