merge into的用法

[qixingl]

找的资料都是判断一个表中有没有数据而去插入或更新另外一个表，我想用的是，就是判断本表中有没有某条数据，有就更新，没有就插入，用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)

[cosio]

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)


select * from usermc_cache_t where sid='8000000044' and mcid='000005' 先把这些数据移到另一个临时表中试试看!

[小灰狼W]

那你将(select * from usermc_cache_t where sid='8000000044' and mcid='000005')
替换成(select '8000000044' sid,'000005' mcid from dual)
否则你的b视图可能会为空，when不成立

[bluelamb]

merge into usermc_cache_t a
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)

[zcs_1]

try please

merge into usermc_cache_t a 
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)

[超叔csdn]

when not matched then insert(a.sid,a.mcid,a.oprtype,a.oprdate) values('8000000044','000005','A',sysdate)

这里的字段列举不用加a.什么
when not matched then insert(sid,mcid,oprtype,oprdate) values('8000000044','000005','A',sysdate)