大家好，我想问大家一个ORACLE9i中merge into 的问题

[cn_a380]

大家好，在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)

[小灰狼W]

9i不写不行
直接用insert into into pos_points a select .... from ..
吧

[cn_a380]

狂浪大哥，可是直接写insert into 的话，可能出现主键冲突啊，那怎么办啊，大哥帮我写个例子啊

[cn_a380]

大哥，要是我用
when matched then
update set a.points=a.points
行不行啊？

[liusong_china]

[Quote=引用 3 楼 cn_a380 的回复:]
大哥，要是我用
when matched then
update set a.points=a.points
行不行啊？
[/Quote]

那还不如用：
when matched then
update set a.points=a.points where 1 = 2

-----这样就不会去实际更新数据了。。。。。。。

[inthirties]

9i也不能带where

关于merge的用法和10g的新功能你可以参考一下http://blog.csdn.net/inthirties/archive/2009/10/28/4731930.aspx

用3楼的

[wh62592855]

when matched then
null;
when not matched then
...

看看这样行不

[小灰狼W]

insert into pos_points
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)