How to Correctly Perform an UPDATE Query with INNER JOIN in Oracle?

In the Oracle, use Inner Join to correctly execute the update query

The following queries in MySQL can be successfully executed:

However, the same query in Oracle will cause the following errors:

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

Solution

<code>SQL错误：ORA-00933：SQL命令未正确结束
00933. 00000 -  "SQL command not properly ended"</code>

The syntax used in the query is invalid in Oracle. In order to meet the expected results, you can use one of the following methods:

The first method

This method uses sub -query to obtain the update value of table1.value according to the connection conditions. It ensures that only the matching line is updated.

The second 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 method uses an updated internal linkage diagram to execute the update. It requires Oracle to think that the internal linkage view is updated, depending on certain rules.

The above is the detailed content of How to Correctly Perform an UPDATE Query with INNER JOIN in Oracle?. For more information, please follow other related articles on the PHP Chinese website!