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":无效的标识符,怎么看都不明白为什么
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":无效的标识符,怎么看都不明白为什么
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 t113
24
select * from bd_role(role);
--results:
brid brname
10 管理员----
select * from bd_userrole(role & users)
--results:
burbrid(role id) burempid(userid)
--no results
-
提示出错的底方
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 t11 3
一不用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