故障009：改写多表关联同时更新且互换列值

1. 问题描述

某公司开发人员迁移适配达梦数据库，遇到多表关联更新并且同时更新多张表数据源，最终目的是交换同字段列值，特别之处是符合条件的互换跨行字段值。
当时翻阅SQL官方手册，比较数据库的版本，属于二二年九月份非常新的版本，直接搬码SQL语句到达梦数据库执行会报错'-2965 多表更新时仅支持更新同一个表上的列'，此问题一直僵持他们一个月左右。
起先，有一个人在本人QQ技术群上询问，当时有点忙搁置那儿没管，再过一段时间又有一人询问类似问题，恰逢手头不忙，专门翻看发来的SQL，一眼望上去，感觉很不符合常理的交换值，就是后边贴的“样例初型”。随后，让他们发一个完整的SQL“样例成型”，再看谓词条件“item1.iOrder < item2.iOrder”，目标结果清晰可知。

事先声明： 目前2022年9月之前的版本暂不支持多表关联同时更新多个数据源表，MySQL兼容参数即使开启，也无用，但并不代表此语法特性将来不支持， 需要公司内部向研发申请新增此更新功能。

本人一贯作风，先描述现场的情况，再上问题截图，直奔主题，介绍神之手法。

-- MySQL数据库（样例初型）
update billitem_base item1 inner join billitem_base item2 on item1.tenant_id = item2.tenant_id
set item1.iOrder = item2.iOrder,item2.iOrder = item1.iOrder;

-- MySQL数据库（样例成型）
update billitem_base item1 inner join billitem_base item2 on item1.tenant_id = item2.tenant_id
set item1.iOrder = item2.iOrder,item2.iOrder = item1.iOrder
where item1.iBillId in (select id from bill_base where cbillno = 'fdtr_allocate_strategy')
and item1.iTplId in (select tpl.id from billtemplate_base tpl inner join bill_base base on tpl.iBillId = base.id where base.cbillno = 'fdtr_allocate_strategy' and tpl.iTplMode = 0)
and item1.cName = 'paymentAccountType'
and item2.iBillId in (select id from bill_base where cbillno = 'fdtr_allocate_strategy')
and item2.iTplId in (select tpl.id from billtemplate_base tpl inner join bill_base base on tpl.iBillId = base.id where base.cbillno = 'fdtr_allocate_strategy' and tpl.iTplMode = 0)
and item2.cName = 'payment_currency_name'
and item1.iOrder < item2.iOrder;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

---

2. 解决手段

2.1 解决思路

1.乍看SQL源码中存在很多表的重复扫描和关联，通过CTE语法提取公共部分。
2.减少冗余代码，加强代码逻辑结构清晰，递进且有层次感。
3.公共部分提前过滤留下少量的数据，进行后续的过滤操作及数据关联更新。
4.主要目标更新跨行字段交换值更新，当前版本无法支持多表关联多表列同时更新，换用缓存ROWID伪列记录成列表。
5.既然是数据两者之间交换，完全没必要限制在更新设置值的死胡同，人为理解性地把列值对调取可理解性的别名表示，形成类似PLSQL的关联数组的集合（rowid-交换值的键值对）。
6.存放类似键值对的列表结果集，原来考虑过字符串拼接、拆分，完成最终列转行，再次深思熟虑，其实不然，如果遇到超大规模的数据量交换，拼接拆分函数是有限制的，并且发到性能也不高。正当绞尽脑汁思考列转行的最佳方案时，回头翻阅列转行的原始另类方法UNION ALL联合结果集，幡然醒悟！！！
7.既然已经缓存“rowid-交换值”成临时表，再拿它同需更新的目标表进行rowid关联岂不快哉，妙哉！巧妙地把多表同时关联更新转化为同一时刻更新一张源表。

2.2 解决过程

2.2.1 率先调整SQL语句，构造交换数据源

TIP：提取公共项代码，减少表重复扫描，尽量提前过滤掉大量数据，并且采用CTE语法格式，优化逻辑层次结构，提高访问效率。

首次检索出符合待交换的目标数据，事前记录（截图）交换前的状态，以作交换前后的参照对比。
交换预期结果：rowid(70) iOrder(22.0) 与 rowid(2) iOrder(24.0) 这两行的iOrder数据需进行交换更新。

2.2.2 按rowid缓存列表关联，实现跨行更新交换字段值

特别注意：
update支持多表引用关联更新（即：update A表引用, B表引用...），是在8.1.1.190版本后开始支持。
如果比8.1.1.190版本更靠前，请参考我的博客文章 《玩转达梦の多表关联更新改写》

update CTMFDTR.billitem_base o,
(
with x0 as (
select i.rowid rid,i.tenant_id, i.iOrder, i.cName from CTMFDTR.billitem_base i
where 
i.iBillId in (select id from CTMFDTR.bill_base where cbillno = 'fdtr_allocate_strategy')
and i.iTplId in 
 (select tpl.id from CTMFDTR.billtemplate_base tpl 
    inner join CTMFDTR.bill_base base 
    on tpl.iBillId = base.id 
    where base.cbillno = 'fdtr_allocate_strategy' and tpl.iTplMode = 0)
), 
i1 as (
 select * from x0 where cName = 'paymentAccountType'
),
i2 as (
 select * from x0 where cName = 'payment_currency_name' 
)

select i1.rid rid, i2.iOrder iiOrder 
from i1 join i2 on i1.tenant_id = i2.tenant_id
where i1.iOrder < i2.iOrder
 union all
select i2.rid rid,i1.iOrder iiOrder   
from i1 join i2 on i1.tenant_id = i2.tenant_id
where i1.iOrder < i2.iOrder
) x
set o.iOrder = x.iiOrder
where o.rowid = x.rid;


-- before update
--select  iOrder, rowid from CTMFDTR.billitem_base where rowid in(70, 2);
/*
iOrder   ROWID
24.0      2
22.0      70
*/

-- after update
/*
iOrder    ROWID
22.0       2
24.0       70
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

成功解决截图

---

3. 个人闲吹

• 多翻看手册、勤于思考，尽力自己动手，实在不行的情况下，加QQ技术群 940124259 技术讨论。
• 所有的数据库都有着自己一套独特的个性，不能墨守成规，以平日所学，多角度思考问题。
• 每天与大家分享一点技术，丰富知识库，同时加快国产达梦数据库的高速发展。