--- title: OceanBase 数据库 Oracle 模式中如何改写 update xxx set ... from ... 语句-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于OceanBase 数据库 Oracle 模式中如何改写 update xxx set ... from ... 语句相关的常见问题和使用技巧，帮助您快速解决OceanBase 数据库 Oracle 模式中如何改写 update xxx set ... from ... 语句的难题。 image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # OceanBase 数据库 Oracle 模式中如何改写 update xxx set ... from ... 语句 更新时间：2026-05-28 02:06 适用版本： V1.4.x、V2.1.x、V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x 内容类型：How-to 外部客户的业务应用从其它数据库迁移到 OceanBase 数据库时，不可避免的会遇到原数据库系统和 OceanBase 数据库系统不兼容的 SQL 语法，这种情况下，需要客户 DBA/业务开发人员对原始应用代码中使用的 SQL 语句进行等价性改写，以实现数据库迁移的目标。 本文主要介绍如何对 GaussDB/PostgreSQL/SQL Server 中支持的 `update set =... from ...` 语句在 OceanBase 数据库 Oracle 租户中进行等价改写。 ## 详细说明 原生 Oracle/原生 MySQL/OceanBase 均不支持 `update set =... from ...` 语句的写法，而 GaussDB/PostgreSQL/SQL Server 均支持该写法，因此，需要针对这类SQL语句进行等价改写。 下面详细介绍测试数据的准备以及几种常见的等价改写和容易误导的非等价改写。 ### 准备测试数据 ```shell obclient(TEST@oracle)[TEST]> CREATE TABLE tbl1 (col1 INT, col2 INT, name varchar(10)); Query OK, 0 rows affected (4.352 sec) obclient(TEST@oracle)[TEST]> CREATE TABLE tbl2 (col1 INT, col2 INT, address varchar(10)); Query OK, 0 rows affected (0.156 sec) obclient(TEST@oracle)[TEST]> INSERT INTO tbl1 VALUES(0, 0, 'a'),(1, null, 'b'),(2, null, 'c'),(3, null, 'd'),(4, 50, 'k'),(5, 50, 'k'); Query OK, 6 rows affected (0.014 sec) Records: 6 Duplicates: 0 Warnings: 0 obclient(TEST@oracle)[TEST]> INSERT INTO tbl2 VALUES(1, 1, 'h'),(2, 20, 'i'),(3, 3, 'j'),(4, 40, 'i'),(4, 0, 'k'),(5, 0, 'k'); Query OK, 6 rows affected (0.011 sec) Records: 6 Duplicates: 0 Warnings: 0 obclient(TEST@oracle)[TEST]> commit; Query OK, 0 rows affected (0.002 sec) obclient(TEST@oracle)[TEST]> select * from tbl1; +------+------+------+ | COL1 | COL2 | NAME | +------+------+------+ | 0 | 0 | a | | 1 | NULL | b | | 2 | NULL | c | | 3 | NULL | d | | 4 | 50 | k | | 5 | 50 | k | +------+------+------+ 6 rows in set (0.030 sec) obclient(TEST@oracle)[TEST]> select * from tbl2; +------+------+---------+ | COL1 | COL2 | ADDRESS | +------+------+---------+ | 1 | 1 | h | | 2 | 20 | i | | 3 | 3 | j | | 4 | 40 | i | | 4 | 0 | k | | 5 | 0 | k | +------+------+---------+ 6 rows in set (0.021 sec) ``` ### GaussDB/PG/SQL Server 中的原始 SQL ```shell kgbp1=> update tbl1 set tbl1.col2 = tbl2.col2 from tbl2 where tbl1.col1 = tbl2.col1 and (tbl2.address = 'i' or tbl2.col2 > 0); UPDATE 4 kgbp1=> select * from tbl1; col1 | col2 | name ------+------+------ 0 | 0 | a 1 | 1 | b 2 | 20 | c 3 | 3 | d 4 | 40 | k 5 | 50 | k (6 rows) kgbp1=> ``` 直接在 OceanBase 数据库 Oracle 租户中执行会遇到语法报错（OceanBase MySQL 租户、原生 Oracle、原生 MySQL 类似）： ```shell obclient(TEST@oracle)[TEST]> update tbl1 set tbl1.col2 = tbl2.col2 from tbl2 where tbl1.col1 = tbl2.col1 and (tbl2.address = 'i' or tbl2.col2 > 0); ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'from tbl2 where tbl1.col1 = tbl2.col1 and (tbl2.address = 'i' or tbl2.col2 > 0)' at line 3 ``` ### 等价改写一 ```shell obclient(TEST@oracle)[TEST]> MERGE INTO tbl1 USING tbl2 ON (tbl1.col1 = tbl2.col1 and (tbl2.address = 'i' or tbl2.col2 > 0)) WHEN MATCHED THEN UPDATE SET tbl1.col2 = tbl2.col2; Query OK, 4 rows affected (0.021 sec) obclient(TEST@oracle)[TEST]> select * from tbl1; +------+------+------+ | COL1 | COL2 | NAME | +------+------+------+ | 0 | 0 | a | | 1 | 1 | b | | 2 | 20 | c | | 3 | 3 | d | | 4 | 40 | k | | 5 | 50 | k | +------+------+------+ 6 rows in set (0.001 sec) ``` ### 等价改写二 ```shell obclient(TEST@oracle)[TEST]> MERGE INTO tbl1 USING tbl2 ON (tbl1.col1 = tbl2.col1) WHEN MATCHED THEN UPDATE SET tbl1.col2 = tbl2.col2 where tbl2.address = 'i' or tbl2.col2 > 0; Query OK, 4 rows affected (0.009 sec) ``` ### 等价改写三 ```shell obclient(TEST@oracle)[TEST]> update tbl1 a set a.col2 = (select b.col2 from tbl2 b where a.col1 = b.col1 and (b.address = 'i' or b.col2 > 0) fetch first 1 row only) where exists (select 1 from tbl2 b where a.col1 = b.col1 and (b.address = 'i' or b.col2 > 0)); Query OK, 4 rows affected (0.017 sec) Rows matched: 4 Changed: 4 Warnings: 0 ``` ### 不等价改写一 ```shell obclient(TEST@oracle)[TEST]> update tbl1 a set a.col2 = (select b.col2 from tbl2 b where a.col1=b.col1 and (b.address = 'i' or b.col2 > 0)); Query OK, 6 rows affected (0.011 sec) Rows matched: 6 Changed: 6 Warnings: 0 obclient(TEST@oracle)[TEST]> select * from tbl1; +------+------+------+ | COL1 | COL2 | NAME | +------+------+------+ | 0 | NULL | a | | 1 | 1 | b | | 2 | 20 | c | | 3 | 3 | d | | 4 | 40 | k | | 5 | NULL | k | +------+------+------+ 6 rows in set (0.001 sec) ``` #### 说明 上面这个 update 改写不等价，因为直接 update 会把 tbl1 表中和 tbl2 表中连接不上的行也更新成 NULL了。 ### 不等价改写二 ```shell obclient(TEST@oracle)[TEST]> update tbl1 a set a.col2 = (select b.col2 from tbl2 b where a.col1 = b.col1) where exists (select 1 from tbl2 b where a.col1 = b.col1 and (b.address = 'i' or b.col2 > 0)); ORA-01427: single-row subquery returns more than one row obclient(TEST@oracle)[TEST]> select * from tbl1; +------+------+------+ | COL1 | COL2 | NAME | +------+------+------+ | 0 | 0 | a | | 1 | NULL | b | | 2 | NULL | c | | 3 | NULL | d | | 4 | 50 | k | | 5 | 50 | k | +------+------+------+ 6 rows in set (0.001 sec) ``` #### 说明 上面这个 update 改写不等价，因为第一个括号里面的子查询可能会报错：`ORA-01427: single-row subquery returns more than one row`。 ### 不等价改写三 ```shell obclient()[TEST]> update tbl1 a set a.col2 = (select b.col2 from tbl2 b where a.col1 = b.col1 and (b.address = 'i' or b.col2 > 0)) where exists (select 1 from tbl2 b where a.col1 = b.col1); Query OK, 5 rows affected (0.016 sec) Rows matched: 5 Changed: 5 Warnings: 0 obclient(TEST@oracle)[TEST]> select * from tbl1; +------+------+------+ | COL1 | COL2 | NAME | +------+------+------+ | 0 | 0 | a | | 1 | 1 | b | | 2 | 20 | c | | 3 | 3 | d | | 4 | 40 | k | | 5 | NULL | k | +------+------+------+ ``` #### 说明 上面这个 update 改写也不等价，因为如果 tbl1 中的一行匹配了后面的 `where a.col1 = b.col1` 但是匹配不了前面的 `where a.col1 = b.col1 and (b.address = 'i' or b.col2 > 0)` 的话，会导致 tbl1 中的这行也被更新成 NULL。 ## 备注说明 - 进行 SQL 语句等价改写时，一定要注意语句改写前后的语义等价性，需要从 SQL 语句解析的语义逻辑和大数据量实测两方面确保改写前后的等价性，避免出现隐蔽较深的不等价改写。 - 不同等价改写的执行效率可能不同，尽量选择执行效率高的等价改写，比如上面这几种等价写法中 merge 的执行效率会好一些（可以从 explain 执行计划中查看比较出来）。 ```shell obclient(TEST@oracle)[TEST]> explain MERGE INTO tbl1 USING tbl2 ON (tbl1.col1 = tbl2.col1 and (tbl2.address = 'i' or tbl2.col2 > 0)) WHEN MATCHED THEN UPDATE SET tbl1.col2 = tbl2.col2; +----------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------+ | =================================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | --------------------------------------------------- | | |0 |MERGE | |2 |10 | | | |1 |└─HASH JOIN | |2 |10 | | | |2 | ├─TABLE FULL SCAN|TBL2|2 |5 | | | |3 | └─TABLE FULL SCAN|TBL1|6 |5 | | | =================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output(nil), filter(nil) | | columns([{TBL1: ({TBL1: (TBL1.__pk_increment, TBL1.COL1, TBL1.COL2, TBL1.NAME)})}]), partitions(p0), | | update([TBL1.COL2=column_conv(NUMBER,PS:(-1,0),NULL,TBL2.COL2)]), | | insert_conds(nil), update_conds(nil), delete_conds(nil) | | 1 - output([TBL1.__pk_increment], [TBL1.COL1], [TBL1.COL2], [TBL1.NAME], [TBL2.COL2]), filter(nil), rowset=16 | | equal_conds([TBL1.COL1 = TBL2.COL1]), other_conds(nil) | | 2 - output([TBL2.COL1], [TBL2.COL2]), filter([TBL2.ADDRESS = cast('i', VARCHAR2(1048576 )) OR TBL2.COL2 > 0]), rowset=16 | | access([TBL2.COL1], [TBL2.ADDRESS], [TBL2.COL2]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([TBL2.__pk_increment]), range(MIN ; MAX)always true | | 3 - output([TBL1.__pk_increment], [TBL1.COL1], [TBL1.COL2], [TBL1.NAME]), filter(nil), rowset=16 | | access([TBL1.__pk_increment], [TBL1.COL1], [TBL1.COL2], [TBL1.NAME]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([TBL1.__pk_increment]), range(MIN ; MAX)always true | +----------------------------------------------------------------------------------------------------------------------------+ 24 rows in set (0.010 sec) obclient(TEST@oracle)[TEST]> obclient(TEST@oracle)[TEST]> explain update tbl1 a set a.col2 = (select b.col2 from tbl2 b where a.col1 = b.col1 and (b.address = 'i' or b.col2 > 0)) where exists (select 1 from tbl2 b where a.col1 = b.col1 and (b.address = 'i' or b.col2 > 0)); +---------------------------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------------------------+ | ========================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------------- | | |0 |UPDATE | |2 |51 | | | |1 |└─SUBPLAN FILTER | |2 |16 | | | |2 | ├─HASH RIGHT SEMI JOIN | |2 |10 | | | |3 | │ ├─SUBPLAN SCAN |VIEW1|2 |5 | | | |4 | │ │ └─TABLE FULL SCAN |B |2 |5 | | | |5 | │ └─TABLE FULL SCAN |A |6 |5 | | | |6 | └─TABLE FULL SCAN |B |1 |5 | | | ========================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output(nil), filter(nil) | | table_columns([{A: ({TBL1: (A.__pk_increment, A.COL1, A.COL2, A.NAME)})}]), | | update([A.COL2=column_conv(NUMBER,PS:(-1,0),NULL,subquery(1))]) | | 1 - output([A.__pk_increment], [A.COL1], [A.COL2], [A.NAME], [subquery(1)]), filter(nil), rowset=16 | | exec_params_([A.COL1(:0)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false | | 2 - output([A.__pk_increment], [A.COL1], [A.COL2], [A.NAME]), filter(nil), rowset=16 | | equal_conds([A.COL1 = VIEW1.B.COL1]), other_conds(nil) | | 3 - output([VIEW1.B.COL1]), filter(nil), rowset=16 | | access([VIEW1.B.COL1]) | | 4 - output([B.COL1]), filter([B.ADDRESS = cast('i', VARCHAR2(1048576 )) OR B.COL2 > 0]), rowset=16 | | access([B.COL1], [B.ADDRESS], [B.COL2]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([B.__pk_increment]), range(MIN ; MAX)always true | | 5 - output([A.__pk_increment], [A.COL2], [A.COL1], [A.NAME]), filter(nil), rowset=16 | | access([A.__pk_increment], [A.COL2], [A.COL1], [A.NAME]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([A.__pk_increment]), range(MIN ; MAX)always true | | 6 - output([B.COL2]), filter([:0 = B.COL1], [B.ADDRESS = cast('i', VARCHAR2(1048576 )) OR B.COL2 > 0]), rowset=16 | | access([B.COL1], [B.ADDRESS], [B.COL2]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false], | | range_key([B.__pk_increment]), range(MIN ; MAX)always true | +---------------------------------------------------------------------------------------------------------------------+ 34 rows in set (0.011 sec) ``` ## 影响租户 影响 OceanBase 数据库中的 Oracle 租户，对于 SYS 租户和 MySQL 租户无影响。 ## 适用版本 OceanBase 数据库所有版本。 上一篇 [打开 _enable_enhanced_cursor_validation 后 DML 修改的表个数超过 8 个则 cursor 可能读不到事务内修改](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003228213) 下一篇 [merge into 临时表时在计划执行阶段触发报错-4016 问题排查](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000006830281) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线