MySQL的update...inner join...on语法到HGDB的迁移(APP)

环境

系统平台：Linux x86-64 Red Hat Enterprise Linux 7
版本：4.5

文档用途

客户的源库是MySQL，SQL语句中使用了update tableA inner join tableB on 条件 set …的语法，

即tableA和tableB结合，用tableB的字段值更新tableA的字段。示例如下：

①inner join的写法

update tb_user t1

inner join (

select * from tb_user where dt = 'bl'

) t2

on t1.dt = 'sf' and t1.pkfi = t2.pkfi

set t1.name = t2.name, t1.age = t2.age, t1.sex = t2.sex;

②不使用inner join的写法

update tb_user t1

, (

select * from tb_user where dt = 'bl'

) t2

set t1.name = t2.name, t1.age = t2.age, t1.sex = t2.sex

where t1.dt = 'sf' and t1.pkfi = t2.pkfi;

在HGDB中暂时不兼容该语法，需要通过修改SQL语句实现。即把inner join后面的语句，一直到on的条件语句，

都移动到set的后面，然后inner join改成from，on改成where。修改后如下：

update tb_user t1

set t1.name = t2.name, t1.age = t2.age, t1.sex = t2.sex

from (

select * from tb_user where dt = 'bl'

) t2

where t1.dt = 'sf' and t1.pkfi = t2.pkfi;

详细信息

MySQL的update…inner join…on语法到HGDB的迁移示例：

测试数据准备：

create table tb_user(id int,name varchar(20),age int,sex varchar(10),dt varchar(2),pkfi int ,primary key(id));

insert into tb_user values(1,'张三1','10','男','sf',1);

insert into tb_user values(2,'张三2','20','女','sf',2);

insert into tb_user values(3,'张三3','30','男','sf',3);

insert into tb_user values(4,'张三4','40','女','sf',4);

insert into tb_user values(5,'李四1','50','女','bl',1);

insert into tb_user values(6,'李四2','60','男','bl',2);

insert into tb_user values(7,'李四3','70','女','bl',3);

insert into tb_user values(8,'李四4','80','男','bl',4);

MySQL测试：

①执行检索，查看更新前数据

②执行更新

update tb_user t1

inner join (

select * from tb_user where dt = 'bl'

) t2

on t1.dt = 'sf' and t1.pkfi = t2.pkfi

set t1.name = t2.name, t1.age = t2.age, t1.sex = t2.sex;

③查看更新结果，dt字段是sf的记录被更新了

HGDB中替换方案测试：

①执行检索，查看更新前数据

②执行更新

update tb_user t1

set t1.name = t2.name, t1.age = t2.age, t1.sex = t2.sex

from (

select * from tb_user where dt = 'bl'

) t2

where t1.dt = 'sf' and t1.pkfi = t2.pkfi;

③查看更新结果，dt字段是sf的记录被更新了