How to Efficiently Update Oracle Values Using Joins?

Updating Oracle Values with Join

Updating tables using joins can sometimes lead to complexities. One such instance where users encounter exceptions is when attempting to update an amount using a join. For example:

UPDATE tab1
   SET tab1.total_adjusted_cost = tab1.total_adjusted_cost + t1.total
 FROM table1 tab1, 
      (SELECT tab3.name, tab3.add, SUM(tab2.amount) AS total
         FROM table2 tab2,
              table3 tab3,
              table4 tab4
        WHERE tab2.id = tab3.id
          AND tab3.id = tab4.id
          AND tab4.indicator = 'Y'
        GROUP BY tab3.name, tab3.add ) t1
WHERE tab1.id = t1.id;

Executing this query may result in the error: "SQL command not properly ended."

Solution Using Merge Statement

To resolve this issue, consider using the merge statement instead:

merge into table1 tab1 
using
(
SELECT tab3.name, tab3."add", SUM(tab2.amount) AS total
  FROM table2 tab2,
    table3 tab3 ,
    table4 tab4
  WHERE tab2.id        = tab3.id
  AND tab3.id            = tab4.id
  AND tab4.indicator             ='Y'
  GROUP BY tab3.name,
    tab3."add"
)t1
on(tab1.id      = t1.id)
when matched then 
update set tab1.total_adjusted_cost = tab1.total_adjusted_cost + t1.total

The merge statement allows for more concise and efficient updates by combining the insert, update, and delete statements into a single operation.

The above is the detailed content of How to Efficiently Update Oracle Values Using Joins?. For more information, please follow other related articles on the PHP Chinese website!