mergeinto应用:实现 无则insert插入,有则update更新,一条语句直接进行insert/update操作
merge ino语法:
MERGE INTO table1 alias1USING (table2|view2|sub_query2) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE table1
SET col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT(column_list)VALUES(column_values);
--将总公司sstl的客户信息occ_file数据表和子公司sstl_dx的客户信息occ_file核对 --如果sstl_dx公司的客户信息和sstl不同,则依据sstl客户信息更新sstl_dx的客户信息 --如果sstl_dx公司没有,则将sstl的客户信息新增入sstl_dx公司 MERGE INTO sstl_dx.occ_file dx_occ USING sstl.occ_file sstl_occ ON(dx_occ.occ01 = sstl_occ.occ01) WHEN MATCHED THEN update set dx_occ.occ02 = sstl_occ.occ02,dx_occ.occ03 = sstl_occ.occ03,dx_occ.occ04 = sstl_occ.occ04,dx_occ.occ05 = sstl_occ.occ05,... where dx_occ.occacti = 'Y' WHEN NOT MATCHED THEN insert values(sstl_occ.occ01,sstl_occ.occ02,sstl_occ.occ03,sstl_occ.occ04,sstl_occ.occ05,...) where sstl_occ.occacti = 'Y';原文链接:https://www.f2er.com/oracle/206860.html