一个项目中要使用merge into的语法更新一张表的数据做复制，速度还是比较快的。

点击(此处)折叠或打开

merge into pharmacy.DRUG_PRESC_DETAIL bb --需要更新的表
using (select * from pharmacy.DRUG_PRESC_DETAIL@server1 c where c.PRESC_DATE >= sysdate -100) dd --相关联的的表
on(bb.PRESC_DATE = dd.PRESC_DATE and bb.PRESC_NO = dd.PRESC_NO and bb.ITEM_NO = dd.ITEM_NO) --定义的规则
when matched then
update
set
bb.DRUG_CODE = dd.DRUG_CODE,
bb.DRUG_SPEC = dd.DRUG_SPEC,
bb.DRUG_NAME = dd.DRUG_NAME,
bb.FIRM_ID = dd.FIRM_ID,
bb.PACKAGE_SPEC = dd.PACKAGE_SPEC,
bb.PACKAGE_UNITS = dd.PACKAGE_UNITS,
bb.QUANTITY = dd.QUANTITY,
bb.COSTS = dd.COSTS,
bb.PAYMENTS = dd.PAYMENTS,
bb.ORDER_NO = dd.ORDER_NO,
bb.ITEM_SNO = dd.ITEM_SNO,
bb.ORDER_SUB_NO = dd.ORDER_SUB_NO,
bb.ADMINISTRATION = dd.ADMINISTRATION,
bb.FLAG = dd.FLAG
when not matched then
insert
values
（dd.PRESC_DATE,
dd.PRESC_NO,
dd.ITEM_NO,
dd.DRUG_CODE,
dd.DRUG_SPEC,
dd.DRUG_NAME,
dd.FIRM_ID,
dd.PACKAGE_SPEC,
dd.PACKAGE_UNITS,
dd.QUANTITY,
dd.COSTS,
dd.PAYMENTS,
dd.ORDER_NO,
dd.ITEM_SNO,
dd.ORDER_SUB_NO,
dd.ADMINISTRATION,
dd.FLAG);
commit;

ITPub博客

项目中遇到的merge into的用法，mark