ORACLE 关联表更新

吕小小小布

已于 2023-07-12 10:30:16 修改

阅读量1.2k

点赞数 1

文章标签： oracle 数据库

于 2023-07-12 09:22:08 首次发布

本文链接：https://blog.youkuaiyun.com/yanghuangfdgd/article/details/131673807

版权

同事有个小需求，将A表中的a1字段数据插入到B表中b1字段二者有等值ID字段，可通过ID进行连接，感觉这很简单了，随意就写了如下sql：

update A set a.data = (select b.date from a join b on (a.id = b.id )) ;

但是结果就是，单行子查询返回多行值，因为目标值有多个，此sql只适用于单个结果，于是进行经典百度操作，发现了新大陆即 merge 经验证成功完成

 merge into A using B on (A.id =B.id ) when matched then update set A.data = B.data;

Oracle9i引入了MERGE命令，MERGE取联合之意，你能够在一个SQL语句中对一个表同时执行inserts和updates操作. MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.在Oracle 10g中MERGE有如下一些改进：

1、UPDATE或INSERT子句是可选的

2、UPDATE和INSERT子句可以加WHERE子句

3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表

4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行

也可进行条件插入

MERGE INTO products p  
    USING newproducts np  
    ON (p.product_id = np.product_id)  
    WHEN MATCHED THEN  
    UPDATE  
    SET p.product_name = np.product_name,  
    p.category = np.category  
    WHERE p.category = 'DVD'  
    WHEN NOT MATCHED THEN  
    INSERT  
    VALUES (np.product_id, np.product_name, np.category)  
    WHERE np.category != 'BOOKS'

所以语法总结为：

MERGE INTO 目标表 a  
    USING 源表 b  
    ON (b.product_id = a.product_id)  --关联条件 
    WHEN MATCHED THEN                 --语法 当匹配时进行更新
    UPDATE  
    SET a.product_name = b.product_name,  
    a.category = b.category  
    WHERE a.category = 'DVD'  
    WHEN NOT MATCHED THEN              --语法 当不匹配时进行插入
    INSERT  
    VALUES (b.product_id, b.product_name, b.category)  
    WHERE b.category != 'BOOKS'

PS ：源表中的关联条件值要唯一，否则会报错

ORA-30926: 无法在源表中获得一组稳定的行。

原因比较简单，因为在merge into时需要设定一个key值，会根据这个key值来决定merge into的操作（update还是insert into），所以要求在merge时这个key值是唯一的。所以要先从临时表中选出全部数据而且key值是唯一的。