玩转达梦の 多表关联更新改写

DM技术交流QQ群：940124259

1. 问题描述

本主题围绕多表关联更新目标表，在不同的数据库中语法不尽相似（Oracle/MySQL/MSSQL/DM），但异构数据库迁移后可能遇到个别特殊的语法报错，本着改写SQL方式符合目标数据库，即可规避语法报错。

---

2. DM版本差异语法

达梦数据库随着使用的人群遍及，版本不断升级迭代，增强产品功能，已经在新的版本中开始支持类似上面报错的语法，如此一来，大伙用不着挠头苦想为什么不支持呢？即使最新版本支持，放眼往去，还有很多生产环境使用着旧版本的达梦数据库，考虑到稳定性，不可能轻易随意升级，造成预料之外的结果的话，非常令人尴尬。就此，本人专门写出这一专题，目的是为大家解惑和提供更多可选的技巧，可以灵活应对市面上几乎所有关系型数据库，以不变应万变。

---

3. 准备测试数据

drop table if EXISTS course;
drop table if EXISTS student;

create table course  -- 课程表
(
  cour_id char(3) primary key,
  cour_name varchar(10) not null  
); 

create table student  -- 学生表
(
  stu_id int identity(1,1) primary key,
  stu_name varchar(10) not null,
  stu_age tinyint not null check(stu_age between 0 and 100),
  stu_course_id char(3)  -- foreign key references course(cour_id)    
);


insert into course values('001', '语文'), ('002', '数学'),('003', '英语'), ('004', '体育'),('005', '化学');
insert into student values
    ('小新', 12, '003'), ('小明', 10, '001'), 
    ('小金', 20, '008'), ('小王', 15, '005'), 
    ('小白', 6, '002'), ('小李', 18, '003'),
    ('小吴', 16, '004'), ('小桂', 10, null);
commit;    


select * from course;
select * from student;

---

4. 改写方法

4.1 改写句型1: 内联子查询

/*
技巧：内联子查询，又称派生表，先关联成一张大表，再过滤更新。
*/
update (select * from student s, course c where s.stu_course_id = c.cour_id ) b
set b.stu_age = b.stu_age + 10, b.stu_name = b.stu_name|| '+10'
where b.stu_age >= 10;

4.2 改写句型2: FROM表引用关联

/*
技巧：语法原生支持的，像MSSQL语法。
*/
update student s  -- 更新目标表
set s.stu_age = s.stu_age + 10, s.stu_name = s.stu_name|| '+10'
from course c  -- 源引用表，被关联表
where s.stu_course_id = c.cour_id and s.stu_age >= 10;

-- 执行计划一致，同上
update student s
set s.stu_age = s.stu_age + 10, s.stu_name = s.stu_name|| '+10'
from student s, course c
where s.stu_course_id = c.cour_id and s.stu_age >= 10;

4.3 改写句型3: MERGE综合体

/*
技巧：采用merge using子句关联条件
*/
merge into student s 
using course c on (s.stu_course_id = c.cour_id) 
when matched then 
  update set s.stu_age = s.stu_age + 10, s.stu_name = s.stu_name|| '+10' 
  where s.stu_age >= 10;

4.4 改写句型4: exists半连接

/*
技巧：采用exists半连接方式过滤，比较高效。
*/
update student s 
set s.stu_age = s.stu_age + 10, s.stu_name = s.stu_name|| '+10'
where exists (select 1 from course c where s.stu_course_id = c.cour_id)
and s.stu_age >= 10;

4.5 改写句型5: IN半连接

/*
技巧：采用IN半连接方式过滤，虽然可以实现同样的功能，针对子查询中的数据量大，酌情而定。
*/
update student s 
set s.stu_age = s.stu_age + 10, s.stu_name = s.stu_name|| '+10'
where s.stu_course_id in (select c.cour_id from course c)
and s.stu_age >= 10;

4.6 改写句型6: 多列同步更新

-- error: 因为多列更新中关联子查询可能会返回空值（学生表8号小桂同学课程号为空值，在课程表中找不到匹配的记录），结合stu_age字段定义为非空约束，所以报错。
update student s 
set (s.stu_age, s.stu_name ) 
    = (select s.stu_age + 10, s.stu_name|| '+10' from course c where s.stu_course_id = c.cour_id )
where s.stu_age >= 10;

/*
技巧：弥补非空约束，增加exists谓词条件，主要技巧是多列同步更新中使用关联子查询。
*/
update student s 
set (s.stu_age, s.stu_name ) 
    = (select s.stu_age + 10, s.stu_name|| '+10' from course c where s.stu_course_id = c.cour_id )
where s.stu_age >= 10
and exists (select 1 from course c where s.stu_course_id = c.cour_id);

4.7 改写句型7: 新版原生支持update多表关联

-- 请升级到 8-1-1-190版以后，直接使用以下语句更新。
update student s, course c
set s.stu_age = s.stu_age + 10, s.stu_name = s.stu_name|| '+10'
where s.stu_course_id = c.cour_id and s.stu_age >= 10;