How to Correctly Update Oracle Tables Using Joins and Avoid ORA-00933 Errors?

Updating Oracle Tables using Joins

The objective is to update a table using a join and encounter the error "ORA-00933: SQL command not properly ended."

This occurs because the provided query is not syntactically correct. The query logic is to calculate a total from multiple tables joined by common columns and update the original table with the calculated total.

To resolve this issue, we recommend using a MERGE statement instead, which provides a more explicit way to update data based on conditions. The MERGE syntax aligns with the business rules more efficiently.

Here's a revised query using the MERGE statement:

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:

• Performs a table join to calculate the total for each matching row.
• Uses the ON clause to match rows based on the id column.
• Updates the matched rows in table1 by adding the calculated total to the existing total_adjusted_cost column.

The above is the detailed content of How to Correctly Update Oracle Tables Using Joins and Avoid ORA-00933 Errors?. For more information, please follow other related articles on the PHP Chinese website!