merge into table1 a using(select * from table2 b) on (a.id=b.id) when matched then update set fno='old' when not matched then insert into values(a.id,'new');
MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*0.1)
不好意思,请高手解释一下MERGE是作什么用的,我没有用过
我去掉 as 还是不行的啊(其实加上as没有错,仔细看看教材)merge into mydepartments m using departments d on (d.department_id=m.department_id) when matched then update set m.department_id=d.department_id, m.department_name=d.department_name when not matched then insert values (d.department_id,d.department_name,d.manager_id,d.location_id);ERROR 位于第 2 行: ORA-00904: "M"."DEPARTMENT_ID": 无效的标识符 真的不明白,为什么是无效的标识符?
一定是你的表定义有问题,查查看吧,嘻嘻,我查了资料,merge可以使用as定义别
在on (d.department_id=m.department_id)使用了department_id 就不能update set m.department_id=d.department_id
using(select * from table2 b)
on (a.id=b.id)
when matched then
update set fno='old'
when not matched then
insert into values(a.id,'new');
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1)
on (d.department_id=m.department_id)
when matched then
update set
m.department_id=d.department_id,
m.department_name=d.department_name
when not matched then
insert values (d.department_id,d.department_name,d.manager_id,d.location_id);ERROR 位于第 2 行:
ORA-00904: "M"."DEPARTMENT_ID": 无效的标识符
真的不明白,为什么是无效的标识符?
就不能update set
m.department_id=d.department_id