条件插入

[zheninchangjiang]

merge into bd_userrole a
using (select '10' as roleid,'0105' as userid from dual) b
on ( a.burbrid =b.roleid and a.burempid=b.userid )
when not matched then insert (a.burbrid,a.burempid) values (b.roleid,b.userid)
when matched then
--do nothing
想要达到这种目标怎么办才好

执行的时候老是出 "A"."BURBRID":无效的标识符，怎么看都不明白为什么

[hongqi162]

create table t1(cid int,cname varchar2(100));
insert into t1 values (1,'1');
create table t2(cid int,cname varchar2(100));
insert into t2 values(1,'3');
insert into t2 values(2,'4');
--执行语句
MERGE INTO t1 a
USING t2 b
on (a.cid=b.cid)
WHEN MATCHED THEN
UPDATE SET a.cname=b.cname
WHEN NOT MATCHED THEN
INSERT (a.cid,a.cname ) VALUES ( b.cid,b.cname );
--执行结果
select * from t1

13
24

[zheninchangjiang]

？？？？？？？？？？

[zheninchangjiang]

补充如下：
select * from bd_role(role);
--results:
brid brname
10 管理员

----
select * from bd_userrole(role & users)
--results:
burbrid(role id) burempid(userid)
--no results

[zheninchangjiang]

on ( a.burbrid =b.roleid and a.burempid=b.userid )
－
提示出错的底方

[hongqi162]

create table t1(cid int,cname varchar2(100));
insert into t1 values (1,'1');
create table t2(cid int,cname varchar2(100));
--执行语句
MERGE INTO t1 a
USING (select '1' cid,'3' cname from dual) b
on (a.cid=b.cid)
WHEN MATCHED THEN
UPDATE SET a.cname=b.cname
WHEN NOT MATCHED THEN
INSERT (a.cid,a.cname ) VALUES ( b.cid,b.cname );
--执行结果
select * from t1

1 3

[hongqi162]

要么把你的表结构和测试数据和输出结果发出来

[zheninchangjiang]

问题全搞明白了,
一不用merge
而是用这么一个怪怪的东西
insert into bd_userrole(burbrid,burempid)
select '10','0105' from dual
where not exists(select 1 from bd_userrole where burbrid='10' and burempid='0105')
二是因为merge的限制,不允许在on 的条件例 中进行update

[koo7758258]

学习中!

[zeng_yh]

top