How to Fix ORA-00933 Error When Updating Oracle Tables Using Joins?

Oracle Update Query Using Join: Solving ORA-00933 Error

In an attempt to update the 'total_adjusted_cost' column in table1 using a join with table t1, an ORA-00933 error was encountered. This error indicates that the SQL command was not properly terminated.

To resolve this issue, consider using the MERGE statement instead. The MERGE statement combines the functionality of the INSERT, UPDATE, and DELETE statements into a single operation. Here's how you can update the table 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

In this MERGE statement:

• The USING clause specifies the table t1 that provides the source data for the update.
• The ON clause specifies the join condition between table1 and t1.
• The WHEN MATCHED clause specifies the update operation to be performed on the matched rows.

Using the MERGE statement ensures that the update is performed properly and avoids the ORA-00933 error.

The above is the detailed content of How to Fix ORA-00933 Error When Updating Oracle Tables Using Joins?. For more information, please follow other related articles on the PHP Chinese website!