How to Correctly Update Tables with Inner Joins in Oracle to Avoid ORA-00933?

Oracle UPDATE with INNER JOIN: Avoiding ORA-00933

When updating Oracle tables using an inner join, a common error is ORA-00933: "SQL command not properly ended." This arises from using incorrect syntax. The standard UPDATE ... INNER JOIN ... SET ... syntax used in other databases like MySQL doesn't work directly in Oracle.

The Problem and Oracle's Solution

The problematic syntax looks like this:

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

Oracle requires a different approach. Here's the correct method:

<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>

This uses a subquery to fetch the table2.CODE value for the update and an EXISTS clause to ensure that the update only happens when a matching row exists in table2. This prevents errors caused by attempting to update with non-existent values.

An Alternative Method: Inline Views

Another option involves using an 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>

This method's success depends on whether Oracle considers the inline view updatable, which is governed by specific database rules. The previous method is generally more reliable. Choose the method that best suits your needs and always test thoroughly.

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