找的资料都是判断一个表中有没有数据而去插入或更新另外一个表,我想用的是,就是判断本表中有没有某条数据,有就更新,没有就插入,用merge inot 能实现不?下面就是我判断表usermc_cache_t 中存在sid='8000000044' and mcid='000005'的记录没,有就更新,没有就插入,可执行没效果,问题在那?高手给指点下
merge into usermc_cache_t a
using (select * from usermc_cache_t where sid='8000000044' and mcid='000005') b on(a.sid=b.sid and a.mcid=b.mcid)
when MATCHED then update set oprtype='U',oprdate=sysdate
when not matched then insert(a.sid,a.mcid,a.oprtype,a.oprdate) values('8000000044','000005','A',sysdate)
merge into usermc_cache_t a
using (select * from usermc_cache_t where sid='8000000044' and mcid='000005') b on(a.sid=b.sid and a.mcid=b.mcid)
when MATCHED then update set oprtype='U',oprdate=sysdate
when not matched then insert(a.sid,a.mcid,a.oprtype,a.oprdate) values('8000000044','000005','A',sysdate)
解决方案 »
- Oracle 连接错误;ORA-27101: shared memory realm does not exist
- 请教一个分组语句
- IMP导入出现一些错误,高手帮忙分析下原因!
- 请问哪里有Forms Builder的书下载阿?
- oracle中,经常提示表空间老溢出,在线等
- oracle触发器求助
- 想深入沟通Oracle技术吗?请大家到这里(Oracle技术论坛www.oraclebbs.com)来一起讨论!
- 急救.............(高手快来) ..高分
- 一个我目前一筹莫展的SQL语句。。。
- 关于ORACLE 和.NET 2002的问题
- 关于“ORA-00917: 缺少逗号”错误
- 怎么将SQL2008中的函数和存储过程导入到Oracle中
using (select * from usermc_cache_t where sid='8000000044' and mcid='000005') b on(a.sid=b.sid and a.mcid=b.mcid)
when MATCHED then update set oprtype='U',oprdate=sysdate
when not matched then insert(a.sid,a.mcid,a.oprtype,a.oprdate) values('8000000044','000005','A',sysdate)
select * from usermc_cache_t where sid='8000000044' and mcid='000005' 先把这些数据移到另一个临时表中试试看!
替换成(select '8000000044' sid,'000005' mcid from dual)
否则你的b视图可能会为空,when不成立
using usermc_cache_t b on(a.sid=b.sid and a.mcid=b.mcid and b.sid='8000000044' and b.mcid='000005')
when MATCHED then update set oprtype='U',oprdate=sysdate
when not matched then insert(a.sid,a.mcid,a.oprtype,a.oprdate) values('8000000044','000005','A',sysdate)
using (select '8000000044' as sid1, '000005' as mcid1, 'U' as oprtype1, sysdate as oprdate1 from dual) b on(a.sid=b.sid1 and a.mcid=b.mcid1)
when MATCHED then update set oprtype=b.oprtype1,oprdate=b.operdate1
when not matched then insert(a.sid,a.mcid,a.oprtype,a.oprdate) values(b.sid1,b.mcid1,b.oprtype1,b.oprdate1)
when not matched then insert(sid,mcid,oprtype,oprdate) values('8000000044','000005','A',sysdate)