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

使用 INNER JOIN 更新 Oracle 数据

您遇到的“SQL command not properly ended”错误是因为使用了 MySQL 语法而非 Oracle 语法。 Oracle 更新数据并使用 INNER JOIN 的方法略有不同。

要修正此错误，您可以修改查询如下：

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

In this query, the sub -query in the SET clan is executed for each line in TABLE1, and the value value is updated according to the corresponding row in Table2. WHERE clauses ensure that they only update the line.

or, you can try the following method, depending on the update of the inner associated 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 uses the internal view view to execute the connection, and then use the results to update the basic table.

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