insert into 多张表_多. ( ). 运. 动.

(CSDN博主：写代码也要符合基本法)

多年以后，我仍然忘不了那个夜晚

忘不了那声声尖叫

忘不了那手忙脚乱

忘不了那事后的余温与回味

那本来是一个再平常不过的傍晚，小刘在宿舍煮着再平常不过的面，同事在客厅加着再平常不过的班

突然，同事的一声尖叫划破长空，直让人怀疑他的电脑上不是加班的内容

原来同事写了一条脚本，想把他准备好的备份表里的仅十几条数据，对应更新到生产环境里，然而却不慎更新了生产环境里整张表十几万条数据

并且，10w+ rows updated 这条提示，是老哥提交了事务后看见的

遂尖叫不止，犹如        。

裤子穿利索了我们先来看看老哥的脚本是怎么写的

UPDATE prod_table p   SET p.attribute1 =       (SELECT t.attribute1 FROM back_table t WHERE t.id = p.id)      ,p.attribute2 =       (SELECT t.attribute2 FROM back_table t WHERE t.id = p.id)

乍一看没什么问题，可是细细品问题大了

这样子直接会更新 prod_table 全部的记录！

事实上这样的 SQL 极具迷惑性，尤其是子查询里面条件较为复杂的时候，光是写完 SET 部分就让人血脉喷张气喘吁吁了，很容易就忘记 WHERE 条件，因为同样的 WHERE 条件刚刚在子查询中想了又想敲了又敲

咱们老哥想要写的脚本实际上是这样婶儿的

UPDATE prod_table p   SET p.attribute1 =       (SELECT t.attribute1 FROM back_table t WHERE t.id = p.id)      ,p.attribute2 =       (SELECT t.attribute2 FROM back_table t WHERE t.id = p.id) WHERE EXISTS (SELECT 1 FROM back_table t WHERE t.id = p.id)

如果小刘还是曾经那个初(xiu)出(fa)茅(piao)庐(piao)的代码小白，就算更新一百个字段也会像上例这样写

但如今的小刘，已经是久(mei)经(you)沙(tou)场(fa)的代码大白，如果第一年我的战斗力只有1，经过这三年的历练，我的战斗力不仅是1+1+1这么简单，我现在的战斗力那是1的三次方啊

书归正传，今天我们要讨论的正是可以完全简化上例代码的通过联结视图更新基表

首先我们来改写一下上例的代码

UPDATE (SELECT p.attribute1 p1              ,p.attribute2 p2              ,b.attribute1 b1              ,b.attribute2 b2          FROM back_table b, prod_table p         WHERE b.id = p.id)   SET p1 = b1, p2 = b2

芜湖，代码结构一目了然，更关键的是，绝对不会再影响到不想更新的数据了

当然，如同标量子查询不得返回多行，这里也要保证给 prod_table 每一行返回的更新结果是唯一的

怎么保证呢？back_table 必须使用主键去关联 prod_table，否则就会报错 ORA-01779 cannot modify a column which maps to a non key-preserved table

还有一点需要注意的是，如果需求本身会有目的表中记录不存在源表映射则更新为空值的情况，则要使用外联结关联，同时又要考虑如何在 WHERE 条件中控制受影响的记录范围

注意，无论查询块中有多少张表关联到一起，只能有一张表作为基表被更新，如果写成交叉更新的情况，则会报错  ORA-01776 cannot modify more than one base table through a join view

看到上面这么多条条框框，有些老铁不想这么麻烦，还得建主键什么的，那么退而求其次，至少用子查询更新字段这部分，还有更简单一点的写法

UPDATE prod_table p   SET (p.attribute1, p.attribute2) =       (SELECT t.attribute1, t.attribute2          FROM back_table t         WHERE t.id = p.id) WHERE EXISTS (SELECT 1 FROM back_table t WHERE t.id = p.id)

至少可以少一些复制粘贴

下面我们再来说说多表插入顾名思义，多表插入指的就是将数据一次性插入多张表，这对 PLSQL 存储过程来说意义尤其重大，因为这代表着避免了多次引擎切换先来看看简简单单的语法轨道图

说到多表插入，小刘不由得又回想起了另一个夜晚

那时小刘接手一个外围系统，这个系统的数据结构有一个十分奇妙的地方，比如订单表，一般我们设计数据模型会把订单设计成主细目结构，而这个系统妙就妙在订单表只需一张

怎么就一张呢？比如一个行需要10个数据字段，那么这张订单表就有attribute1到attribute100字段，以便每10个字段一组，组成一个订单行

真是妙蛙种子唱着奇妙能力歌进了米奇妙妙屋，妙到家了

现在我们就假设要把这个奇妙的订单表改造一下，提取出里面的订单行数据插入到新的订单头表和行表中

我们简化一下示例表的结构，假设原表 amazing_order 中 attribute1 和 attribute3 存储订单行号，attribute2 和 attribute4 存储订单行的商品信息

INSERT ALL INTO new_order_header  (header_id, order_number)VALUES  (header_id, order_number)INTO new_order_line  (header_id, line_id, line_number, product_id)VALUES  (header_id, new_order_line_s.nextval, attribute1, attribute2) INTO new_order_line  (header_id, line_id, line_number, product_id)VALUES  (header_id, oe_order_lines_s.nextval, attribute3, attribute4)SELECT ord.header_id      ,ord.order_number      ,ord.attribute1      ,ord.attribute2      ,ord.attribute3      ,ord.attribute4  FROM amazing_order ord

如轨道图所示，多表插入必须使用一个查询结果集作为数据来源，查询中的字段可在多表插入时反复使用或从不使用

细心的朋友就要问了，难道每张订单还都能有两行不成？遇到了只有一行的订单怎么办，岂不是会插入空行？

老铁问的真是太好了，多表插入其实可以实现条件性插入

INSERT ALL WHEN 1 = 1 THEN  INTO new_order_header    (header_id, order_number)  VALUES    (header_id, order_number)WHEN attribute1 IS NOT NULL THEN  INTO new_order_line    (header_id, line_id, line_number, product_id)  VALUES    (header_id, new_order_line_s.nextval, attribute1, attribute2) WHEN attribute2 IS NOT NULL THEN  INTO new_order_line    (header_id, line_id, line_number, product_id)  VALUES    (header_id, oe_order_lines_s.nextval, attribute3, attribute4)SELECT ord.header_id      ,ord.order_number      ,ord.attribute1      ,ord.attribute2      ,ord.attribute3      ,ord.attribute4  FROM amazing_order ord

如上例在每个 INTO 前加写 WHEN 条件即可实现条件插入，一个 WHEN 条件中也可以写多个 INTO 哦

条件多表插入还要注意的是 ALL/FIRST 的区别，ALL 行为是对于查询中的每一行，自上而下检查 WHEN 条件，每个满足的条件块中的插入都会被执行

而在 FIRST 行为中，只有第一个满足的条件块中的插入会被执行

图自网络

今天是大暑节气，又到了一年盛夏，朋友们回味的又是哪个不眠的夜晚呢

---

今天的分享就到这里了，第六次发推没有经验，不会排版，行文也没有条理，以后估计也不会有什么长进，感谢朋友们的鼓励与支持，以后我会坚持下去，求求你们不要取关