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

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

点击(此处)折叠或打开

1. merge into pharmacy.DRUG_PRESC_DETAIL bb    --需要更新的表
   
2. using (select * from pharmacy.DRUG_PRESC_DETAIL@server1 c where c.PRESC_DATE >= sysdate -100) dd  --相关联的的表
   
3. on(bb.PRESC_DATE = dd.PRESC_DATE and bb.PRESC_NO = dd.PRESC_NO and bb.ITEM_NO = dd.ITEM_NO)       --定义的规则
   
4. when matched then
   
5.  update
   
6.     set
   
7.    bb.DRUG_CODE = dd.DRUG_CODE,
   
8.    bb.DRUG_SPEC = dd.DRUG_SPEC,
   
9.    bb.DRUG_NAME = dd.DRUG_NAME,
   
10.    bb.FIRM_ID = dd.FIRM_ID,
    
11.    bb.PACKAGE_SPEC = dd.PACKAGE_SPEC,
    
12.    bb.PACKAGE_UNITS = dd.PACKAGE_UNITS,
    
13.    bb.QUANTITY = dd.QUANTITY,
    
14.    bb.COSTS = dd.COSTS,
    
15.    bb.PAYMENTS = dd.PAYMENTS,
    
16.    bb.ORDER_NO = dd.ORDER_NO,
    
17.    bb.ITEM_SNO = dd.ITEM_SNO,
    
18.    bb.ORDER_SUB_NO = dd.ORDER_SUB_NO,
    
19.    bb.ADMINISTRATION = dd.ADMINISTRATION,
    
20.    bb.FLAG = dd.FLAG
    
21. when not matched then
    
22.  insert
    
23.   values
    
24.    （dd.PRESC_DATE,
    
25.    dd.PRESC_NO,
    
26.    dd.ITEM_NO,
    
27.    dd.DRUG_CODE,
    
28.    dd.DRUG_SPEC,
    
29.    dd.DRUG_NAME,
    
30.    dd.FIRM_ID,
    
31.    dd.PACKAGE_SPEC,
    
32.    dd.PACKAGE_UNITS,
    
33.    dd.QUANTITY,
    
34.    dd.COSTS,
    
35.    dd.PAYMENTS,
    
36.    dd.ORDER_NO,
    
37.    dd.ITEM_SNO,
    
38.    dd.ORDER_SUB_NO,
    
39.    dd.ADMINISTRATION,
    
40.    dd.FLAG);
    
41. commit;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26675752/viewspace-1165619/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26675752/viewspace-1165619/