文章来自: 好喜爱学习网(http://www.haoxiai.net) 网址:http://www.haoxiai.net/shujuku/Oracle/111136.html --语法 MERGE INTO table_name alias1 USING (table|view|sub_query) alias2 ON (join condition) WHEN MATCHED THEN UPDATE table_name SET col1 = col_val1, col2 = col2_val WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values); --实例 在一个同时存在Insert和Update语法的Merge语句中,总共Insert/Update的记录数,就是Using语句中alias2的记录数。 MERGE INTO T T1 USING (SELECT '1001' AS a,2 AS b FROM dual) T2 ON ( T1.a=T2.a) WHEN MATCHED THEN UPDATE SET T1.b = T2.b WHEN NOT MATCHED THEN INSERT (a,b) VALUES(T2.a,T2.b);
如果仅仅是更新的话,不需要那么复杂吧 update a set biz_dl=(select qdl from b where b.biz_id=a.biz_id);
update a set a.biz_dl = (select qdl from b where b.biz_id = a.biz_id and rownum = 1) --rownum=1,保证只返回一条记录,否则返回多条时更新会报错 where exists (select 1 from b where b.biz_id = a.biz_id) --加where条件保证不符合条件的不被更新为null
--语法
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE table_name
SET col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
--实例 在一个同时存在Insert和Update语法的Merge语句中,总共Insert/Update的记录数,就是Using语句中alias2的记录数。
MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN
INSERT (a,b) VALUES(T2.a,T2.b);
如果仅仅是更新的话,不需要那么复杂吧
update a set biz_dl=(select qdl from b where b.biz_id=a.biz_id);
set a.biz_dl = (select qdl
from b
where b.biz_id = a.biz_id
and rownum = 1) --rownum=1,保证只返回一条记录,否则返回多条时更新会报错
where exists (select 1 from b where b.biz_id = a.biz_id) --加where条件保证不符合条件的不被更新为null