在 Oracle 上使用内部联接更新语句

问题描述

我有一个在 MySQL 中运行良好的查询，但是当我在 Oracle 上运行它时出现以下错误:

I have a query which works fine in MySQL, but when I run it on Oracle I get the following error:

SQL 错误:ORA-00933:SQL 命令未正确结束
00933. 00000 - SQL 命令未正确结束"

查询是:

UPDATE table1
INNER JOIN table2 ON table1.value = table2.DESC
SET table1.value = table2.CODE
WHERE table1.UPDATETYPE='blah';

推荐答案

该语法在 Oracle 中无效.你可以这样做:

That syntax isn't valid in Oracle. You can do this:

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

或者你可能能够做到这一点:

Or you might be able to do this:

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

这取决于 Oracle 是否认为内联视图可更新(要更新第二个语句取决于列出的一些规则此处).

It depends if the inline view is considered updateable by Oracle( To be updatable for the second statement depends on some rules listedhere ).

这篇关于在 Oracle 上使用内部联接更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！