How to Fix ORA-00933 Error in Oracle UPDATE Statements with INNER JOINs?

Oracle UPDATE Statements with INNER JOINs: Troubleshooting ORA-00933 Errors

Executing an SQL UPDATE statement incorporating an INNER JOIN in Oracle can sometimes result in an ORA-00933 error ("SQL command not properly ended"). This error, while uncommon in MySQL, frequently occurs in Oracle. Consider this example, which works correctly in MySQL but fails in Oracle:

<code class="language-sql">UPDATE table1
INNER JOIN table2 ON table1.value = table2.DESC
SET table1.value = table2.CODE
WHERE table1.UPDATETYPE='blah';</code>

The solution involves restructuring the Oracle query. Here are two effective methods:

Method 1: Using a Subquery

This approach employs a subquery to update table1:

<code class="language-sql">UPDATE table1 SET table1.value = (SELECT table2.CODE
                                  FROM table2 
                                  WHERE table1.value = table2.DESC)
WHERE table1.UPDATETYPE='blah'
AND EXISTS (SELECT table2.CODE
            FROM table2 
            WHERE table1.value = table2.DESC);</code>

The EXISTS clause ensures that only rows with matching entries in table2 are updated, preventing errors.

Method 2: Utilizing an Updateable Inline View

This method uses an updateable inline view:

<code class="language-sql">UPDATE 
(SELECT table1.value as OLD, table2.CODE as NEW
 FROM table1
 INNER JOIN table2
 ON table1.value = table2.DESC
 WHERE table1.UPDATETYPE='blah'
) t
SET t.OLD = t.NEW</code>

The success of this method depends on Oracle's ability to identify the inline view as updateable. Refer to the Oracle documentation for specific rules governing updateable views. This method might be more efficient than Method 1 in certain scenarios. Choose the method that best suits your specific needs and database configuration.

The above is the detailed content of How to Fix ORA-00933 Error in Oracle UPDATE Statements with INNER JOINs?. For more information, please follow other related articles on the PHP Chinese website!