How to Correctly Update a Table Column Using a JOIN in Oracle SQL?

Oracle Update Query using JOIN

In an attempt to update a column using a JOIN operation, an exception is encountered: ORA-00933: SQL command not properly ended. The following query illustrates the issue:

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;

Solution

To resolve the error, the query should utilize a MERGE statement instead of an UPDATE with a subquery. The MERGE statement allows for both an update and an insert operation in a single statement. The corrected query using MERGE is:

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

This MERGE statement evaluates the rows from table1 and the subquery t1 on the join condition tab1.id = t1.id. When a match is found, it updates the total_adjusted_cost column in table1 by adding the total value from the subquery.

The above is the detailed content of How to Correctly Update a Table Column Using a JOIN in Oracle SQL?. For more information, please follow other related articles on the PHP Chinese website!