我试过了,是因为使用了on条件是e1.empno=e2.empno,所以update的时候就不能再用set e1.empno=e2.empno,其实这也是没必要的了merge into test2 t1 using test1 t2 on (t1.id = t2.id) when matched then update set t1.amount = t2.amount, t1.id = t2.id when not matched then insert (t1.id,t1.amount) values(t2.id,t2.amount)ORA-00904: "T1"."ID": 无效的标识符把set t1.id = t2.id 换成别的列就行了,注意不能是on(...)子句里的列merge into test2 t1 using test1 t2 on (t1.id = t2.id) when matched then update set t1.amount = t2.amount, t1.code = t2.code when not matched then insert (t1.id,t1.amount) values(t2.id,t2.amount)Done
我试过: merge into emp01 as e1 using emp02 e2 on(e1.empno=e2.empno) when matched then update set e1.ename=e2.ename when not matched then insert values(e2.ename); 依然不好使,还是老毛病!!
这样也不行: merge into emp01 as e1 using emp02 e2 on(e1.empno=e2.empno) when matched then update set e1.ename=e2.ename when not matched then insert(e1.ename) values(e2.ename)
这样当然不行啦 merge into emp01 as e1 using emp02 e2 on(e1.empno=e2.empno) when matched then update set e1.ename=e2.ename when not matched then insert(e1.ename) values(e2.ename)这里判断如果e1.empno=e2.empno的话,就更新e1的纪录,这是e1的empno和e2的empno本来是一样的,所以就不要更新,而not matched得时候,就是e1里面不存在e1.empno=e2.empno这条纪录,这时插入的时候就必须要插入e1.empno,这样才能保证插入后e1里存在e1.empno=e2.empno这条纪录
using test1 t2
on (t1.id = t2.id)
when matched then update set t1.amount = t2.amount, t1.id = t2.id
when not matched then insert (t1.id,t1.amount) values(t2.id,t2.amount)ORA-00904: "T1"."ID": 无效的标识符把set t1.id = t2.id 换成别的列就行了,注意不能是on(...)子句里的列merge into test2 t1
using test1 t2
on (t1.id = t2.id)
when matched then update set t1.amount = t2.amount, t1.code = t2.code
when not matched then insert (t1.id,t1.amount) values(t2.id,t2.amount)Done
merge into emp01 as e1
using emp02 e2
on(e1.empno=e2.empno)
when matched then
update set e1.ename=e2.ename
when not matched then
insert values(e2.ename);
依然不好使,还是老毛病!!
merge into emp01 as e1
using emp02 e2
on(e1.empno=e2.empno)
when matched then
update set e1.ename=e2.ename
when not matched then
insert(e1.ename)
values(e2.ename)
哪位大虾告诉我为什么,应为我看到的所有资料里merge语句都支持别名!!
merge into emp01 as e1
using emp02 e2
on(e1.empno=e2.empno)
when matched then
update set e1.ename=e2.ename
when not matched then
insert(e1.ename)
values(e2.ename)这里判断如果e1.empno=e2.empno的话,就更新e1的纪录,这是e1的empno和e2的empno本来是一样的,所以就不要更新,而not matched得时候,就是e1里面不存在e1.empno=e2.empno这条纪录,这时插入的时候就必须要插入e1.empno,这样才能保证插入后e1里存在e1.empno=e2.empno这条纪录
我用的是oracle92
merge into emp01 as e1应为(没有as的)
merge into emp01 e1