首页 > 代码库 > Excel中导入到oracle使用merge into 差异性更新数据库

Excel中导入到oracle使用merge into 差异性更新数据库

merge into temp1 a
using (select ID
         from (Select ID
                 from temp1
                where ID = ‘SYSTEM00000100000010846‘
               union all
               select null ID from dual)
        where rownum = 1) b
On (b.ID = a.ID)
when matched then
  update
     set REPORT_DATE     = to_date(‘2014/5/22 0:00:00‘,
                                   ‘yyyy-mm-dd hh24:mi:ss‘),
         REPORT_SUP_ID   = ‘GY20T0000201000015754595‘,
         REPORT_SUP_NAME = ‘北京军区太原药材供应站2‘,
         VOUCHER         = ‘‘,
         DIGEST          = ‘本日10846‘,
         BORROW_VALUE    = 1000,
         LOAN_VALUE      = -1,
         DIRECTION       = ‘借‘,
         BALANCE         = 2990
when not matched then
  insert
    (ID,
     REPORT_DATE,
     REPORT_SUP_ID,
     REPORT_SUP_NAME,
     VOUCHER,
     DIGEST,
     BORROW_VALUE,
     LOAN_VALUE,
     DIRECTION,
     BALANCE)
  values
    (‘SYSTEM00000100000010846‘,
     to_date(‘2014/5/22 0:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘),
     ‘GY20T0000201000015754595‘,
     ‘北京军区太原药材供应站2‘,
     ‘‘,
     ‘本日10846‘,
     1000,
     -1,
     ‘借‘,
     2990)