YashanDB 支持MySQL多表更新语句的解决方法
前言
MySQL支持多表更新语句，如果迁移到YashanDB，推荐通过兼容性参数来支持。如果兼容性参数支持存在问题的话，也可以按照多表更新的规则进行改写。
问题
在YashanDB默认模式下执行MySQL的多表更新语句，报错YAS-04344 multi-table update is not supported，请看示例：
|
SQL> update t1, t2 set t1.c2=t2.c2 where t1.c1=t2.c1; YAS-04344 multi-table update is not supported |
解决方法
YashanDB使用兼容参数
|
SQL> update t1, t2 set t1.c2=t2.c2 where t1.c1=t2.c1; YAS-04344 multi-table update is not supported SQL> ALTER SYSTEM SET SQL_PLUGIN = 'MYSQL'; Succeed. SQL> update t1, t2 set t1.c2=t2.c2 where t1.c1=t2.c1; 1 rows affected. |
改写多表更新SQL
|
SQL> update t1 set t1.c2=(select t2.c2 from t2 where t2.c1=t1.c1) where exists (select * from t2 where t2.c1 = t1.c1); 1 rows affected. |
常见的改写方法
至于更详细的改写方法，我们举例说明。假设有两张表Area和Branches_Test，表Area定义如下：
|
CREATE TABLE Area( AREA_NO CHAR(2), COUNTRY_NO CHAR(2), AREA_NAME VARCHAR(60), DHQ VARCHAR(20), PRIMARY KEY ("AREA_NO") ) |
表Area数据如下：
|
SQL> select * from Area;
AREA_NO COUNTRY_NO AREA_NAME DHQ ----------------- ----------------- ---------------------------------------------------------------- ---------------------------------------------------------------- 01 CN 华东 Shanghai 02 CN 华西 Chengdu 03 CN 华南 Guangzhou 04 CN 华北 Beijing 05 CN 华中 Wuhan
5 rows fetched. |
表Branches_Test如下：
|
CREATE TABLE Branches_Test( BRANCH_NO CHAR(4), BRANCH_NAME VARCHAR(200) NOT NULL ENABLE, ADDRESS VARCHAR(200), AREA_NO CHAR(2), COUNTRY_NO CHAR(2), AREA_NAME VARCHAR(60), DHQ VARCHAR(20), FOREIGN KEY (AREA_NO) REFERENCES AREA (AREA_NO), PRIMARY KEY (BRANCH_NO) ) |
表Branches_Test数据如下：
|
SQL> select * from Branches_Test;
BRANCH_NO BRANCH_NAME ADDRESS AREA_NO COUNTRY_NO AREA_NAME DHQ ----------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------------- ----------------- ---------------------------------------------------------------- ---------------------------------------------------------------- 0001 深圳 03 CN 0101 上海 上海市静安区 01 CN 0102 南京 City of Nanjing 01 CN 0103 福州 01 CN 0104 厦门 Xiamen 01 CN 0401 北京 04 CN 0402 天津 04 CN 0403 大连 大连市 04 CN 0404 沈阳 04 CN 0201 成都 02 CN 0501 武汉 05 CN 0502 长沙 05 CN
12 rows fetched. |
表Area和Branches_Test共同列是AREA_NO。根据共同列AREA_NO的值，我们会按照表Area的列AREA_NAME和DHQ的值来更新表Branches_Test的列AREA_NAME和DHQ。
1、根据B表的1个共同列来更新A表的1个列
MySQL多表更新语句：
|
update Branches_Test a, Area b set a.AREA_NAME = select b.AREA_NAME where b.AREA_NO = a.AREA_NO; |
YashanDB改写语句：
|
update Branches_Test a set a.AREA_NAME = (select b.AREA_NAME from Area b where b.AREA_NO = a.AREA_NO) where exists (select * from Area b where b.AREA_NO = a.AREA_NO); |
说明：根据表Branches_Test表和表Area的共同列AREA_NO相等的情况下，表Branches_Test表的列AREA_NAME被更新为表Area的列AREA_NAME的值：
|
SQL> select * from Branches_Test;
BRANCH_NO BRANCH_NAME ADDRESS AREA_NO COUNTRY_NO AREA_NAME DHQ ----------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------------- ----------------- ---------------------------------------------------------------- ---------------------------------------------------------------- 0001 深圳 03 CN 华南 0101 上海 上海市静安区 01 CN 华东 0102 南京 City of Nanjing 01 CN 华东 0103 福州 01 CN 华东 0104 厦门 Xiamen 01 CN 华东 0401 北京 04 CN 华北 0402 天津 04 CN 华北 0403 大连 大连市 04 CN 华北 0404 沈阳 04 CN 华北 0201 成都 02 CN 华西 0501 武汉 05 CN 华中 0502 长沙 05 CN 华中
12 rows fetched. |
2、根据B表的1个共同列的来更新A表的2个列
MySQL多表更新语句：
|
update Branches_Test a, Area b set a.DHQ = b.DHQ, a.AREA_NAME = b.AREA_NAME where b.AREA_NO = a.AREA_NO; |
YashanDB改写语句：
|
update Branches_Test a set (a.DHQ, a.AREA_NAME) = (select b.DHQ, b.AREA_NAME from Area b where b.AREA_NO = a.AREA_NO) where exists (select * from Area b where b.AREA_NO = a.AREA_NO); |
说明：根据表Branches_Test表和表Area的共同列AREA_NO相等的情况下，表Branches_Test的列DHQ和列AREA_NAME被更新为表Area的列DHQ和列AREA_NAME的值：
|
SQL> select * from Branches_Test;
BRANCH_NO BRANCH_NAME ADDRESS AREA_NO COUNTRY_NO AREA_NAME DHQ ----------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------------- ----------------- ---------------------------------------------------------------- ---------------------------------------------------------------- 0001 深圳 03 CN 华南 Guangzhou 0101 上海 上海市静安区 01 CN 华东 Shanghai 0102 南京 City of Nanjing 01 CN 华东 Shanghai 0103 福州 01 CN 华东 Shanghai 0104 厦门 Xiamen 01 CN 华东 Shanghai 0401 北京 04 CN 华北 Beijing 0402 天津 04 CN 华北 Beijing 0403 大连 大连市 04 CN 华北 Beijing 0404 沈阳 04 CN 华北 Beijing 0201 成都 02 CN 华西 Chengdu 0501 武汉 05 CN 华中 Wuhan 0502 长沙 05 CN 华中 Wuhan
12 rows fetched. |
3、根据B表的1个共同列和其他列的过滤条件来更新A表的1个列
MySQL多表更新语句：
|
update Branches_Test a, Area b set a.AREA_NAME = b.AREA_NAME where b.AREA_NO = a.AREA_NO and b.DHQ = 'Chengdu'; |
YashanDB改写语句：
|
update Branches_Test a set a.AREA_NAME = (select b.AREA_NAME from Area b where b.AREA_NO = a.AREA_NO) where exists ( select * from Area b where b.AREA_NO = a.AREA_NO and b.DHQ = 'Chengdu' ); |
说明：根据表Branches_Test表和表Area的共同列AREA_NO相等且表Area的列DHQ的值是Chengdu的情况下，表Branches_Test的列AREA_NAME才会被更新为表Area的列AREA_NAME的值，也就是只会更新表Branches_Test的BRANCH_NO=0201的这一行。
|
SQL> select * from Branches_Test;
BRANCH_NO BRANCH_NAME ADDRESS AREA_NO COUNTRY_NO AREA_NAME DHQ ----------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------------- ----------------- ---------------------------------------------------------------- ---------------------------------------------------------------- 0001 深圳 03 CN 0101 上海 上海市静安区 01 CN 0102 南京 City of Nanjing 01 CN 0103 福州 01 CN 0104 厦门 Xiamen 01 CN 0401 北京 04 CN 0402 天津 04 CN 0403 大连 大连市 04 CN 0404 沈阳 04 CN 0201 成都 02 CN 华西 0501 武汉 05 CN 0502 长沙 05 CN
12 rows fetched. |
注意：下面的YashanDB改写语句与上面的YashanDB改写语句不等价，这种写法会更新表Branches_Test的所有行。
|
update Branches_Test a set a.AREA_NAME = (select b.AREA_NAME from Area b where b.AREA_NO = a.AREA_NO and b.DHQ= 'Chengdu'); |
4、根据B表的2个共同列的来更新A表的2个列
MySQL多表更新语句：
|
update Branches_Test a, Area b set a.DHQ = b.DHQ, a.AREA_NAME = b.AREA_NAME where b.AREA_NO = a.AREA_NO and b.COUNTRY_NO = a.COUNTRY_NO; |
YashanDB改写语句：
|
update Branches_Test a set (a.DHQ, a.AREA_NAME) = (select b.DHQ, b.AREA_NAME from Area b where b.AREA_NO = a.AREA_NO and b.COUNTRY_NO = a.COUNTRY_NO); |
说明：根据表Branches_Test表和表Area的共同列AREA_NO和COUNTRY_NO相等的情况下，表Branches_Test的列DHQ和列AREA_NAME被更新为表Area的列DHQ和列AREA_NAME的值：
|
SQL> select * from Branches_Test;
BRANCH_NO BRANCH_NAME ADDRESS AREA_NO COUNTRY_NO AREA_NAME DHQ ----------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------------- ----------------- ---------------------------------------------------------------- ---------------------------------------------------------------- 0001 深圳 03 CN 华南 Guangzhou 0101 上海 上海市静安区 01 CN 华东 Shanghai 0102 南京 City of Nanjing 01 CN 华东 Shanghai 0103 福州 01 CN 华东 Shanghai 0104 厦门 Xiamen 01 CN 华东 Shanghai 0401 北京 04 CN 华北 Beijing 0402 天津 04 CN 华北 Beijing 0403 大连 大连市 04 CN 华北 Beijing 0404 沈阳 04 CN 华北 Beijing 0201 成都 02 CN 华西 Chengdu 0501 武汉 05 CN 华中 Wuhan 0502 长沙 05 CN 华中 Wuhan
12 rows fetched. |
