大家好,在ORACLE9i中使用merge into 如果不需要更新,只需要插入怎么办啊?也就是when mached then 后该怎么写啊,如果不写的话,数据库库不执行
merge into pos_points a
using(select distinct(a.ac_id) ac_id,0 points,to_date(a.opn_date,'yyyymmdd') opn_date,c.br_name opn_br_no,a.name customer_name,d.id_no customer_id,
case when d.id_type='1' then '身份证'
when d.id_type='2' then '户口簿'
when d.id_type='3' then '护照'
when d.id_type='4' then '军人证'
when d.id_type='5' then '回乡证'
when d.id_type='6' then '居住证'
when d.id_type='7' then '驾照'
end id_type,0 count
from dd_mst@dhcc a,mdm_ac_rel@dhcc b,com_branch@dhcc c,cif_id_code_rel@dhcc d
where a.ac_id=b.ac_id and (mdm_code='0020' or mdm_code='0021')
and to_date(a.opn_date,'yyyy-mm-dd')=trunc(sysdate-1)
and a.opn_br_no=c.br_no and a.cif_no=d.cif_no and d.id_type !='N') t
on(a.ac_id=t.ac_id)
when matched then
when not matched then
insert values(t.ac_id,t.points,t.opn_date,t.opn_br_no,t.customer_name,t.customer_id,t.id_type,t.count)
merge into pos_points a
using(select distinct(a.ac_id) ac_id,0 points,to_date(a.opn_date,'yyyymmdd') opn_date,c.br_name opn_br_no,a.name customer_name,d.id_no customer_id,
case when d.id_type='1' then '身份证'
when d.id_type='2' then '户口簿'
when d.id_type='3' then '护照'
when d.id_type='4' then '军人证'
when d.id_type='5' then '回乡证'
when d.id_type='6' then '居住证'
when d.id_type='7' then '驾照'
end id_type,0 count
from dd_mst@dhcc a,mdm_ac_rel@dhcc b,com_branch@dhcc c,cif_id_code_rel@dhcc d
where a.ac_id=b.ac_id and (mdm_code='0020' or mdm_code='0021')
and to_date(a.opn_date,'yyyy-mm-dd')=trunc(sysdate-1)
and a.opn_br_no=c.br_no and a.cif_no=d.cif_no and d.id_type !='N') t
on(a.ac_id=t.ac_id)
when matched then
when not matched then
insert values(t.ac_id,t.points,t.opn_date,t.opn_br_no,t.customer_name,t.customer_id,t.id_type,t.count)
直接用insert into into pos_points a select .... from ..
吧
when matched then
update set a.points=a.points
行不行啊?
那还不如用:
when matched then
update set a.points=a.points where 1 = 2-----这样就不会去实际更新数据了。
null;
when not matched then
...看看这样行不
select distinct(a.ac_id) ac_id,0 points,to_date(a.opn_date,'yyyymmdd') opn_date,c.br_name opn_br_no,
a.name customer_name,d.id_no customer_id,
case when d.id_type='1' then '身份证'
when d.id_type='2' then '户口簿'
when d.id_type='3' then '护照'
when d.id_type='4' then '军人证'
when d.id_type='5' then '回乡证'
when d.id_type='6' then '居住证'
when d.id_type='7' then '驾照'
end id_type,0 count
from dd_mst@dhcc a,mdm_ac_rel@dhcc b,com_branch@dhcc c,cif_id_code_rel@dhcc d
where a.ac_id=b.ac_id and (mdm_code='0020' or mdm_code='0021')
and to_date(a.opn_date,'yyyy-mm-dd')=trunc(sysdate-1)
and a.opn_br_no=c.br_no and a.cif_no=d.cif_no and d.id_type !='N'
and not exists(select 1 from pos_points where a.ac_id=ac_id)