基于湖库一体架构，统一管理结构化、半结构化与非结构化等多模态数据，一个系统承载事务处理、实时分析与 AI 工作负载。
划线反馈
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 <table_name> set <column_name>=... from ... 语句在 OceanBase 数据库 Oracle 租户中进行等价改写。
详细说明
原生 Oracle/原生 MySQL/OceanBase 均不支持 update <table_name> set <column_name>=... from ... 语句的写法，而 GaussDB/PostgreSQL/SQL Server 均支持该写法，因此，需要针对这类SQL语句进行等价改写。
下面详细介绍测试数据的准备以及几种常见的等价改写和容易误导的非等价改写。
准备测试数据
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
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 类似）：
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
等价改写一
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)
等价改写二
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)
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)
等价改写三
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
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)
不等价改写一
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了。
不等价改写二
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。
不等价改写三
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 执行计划中查看比较出来）。
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 数据库所有版本。
