同事有个小需求,将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值是唯一的。