ITPub博客

首页 > 数据库 > Oracle > Oracle 合并 merger into

Oracle 合并 merger into

原创 Oracle 作者:j04212 时间:2014-02-13 11:07:49 0 删除 编辑


merge into copy_emp1 c
  using employees e
  on (c.employee_id=e.employee_id)
when matched then
  update set
  c.first_name=e.first_name,
  c.last_name=e.last_name,
  c.email=e.email,
  c.phone_number=e.phone_number,
  c.hire_date=e.hire_date,
  c.job_id=e.job_id,
  c.salary=e.salary,
  c.commission_pct=e.commission_pct,
  c.manager_id=e.manager_id,
  c.department_id=e.department_id
when not matched then
  insert values(e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,
  e.hire_date,e.job_id,e.salary,e.commission_pct,e.manager_id,e.department_id)


注意:被on关联的列(employee_id)不能被更新,否则会报错ORA-38104:无法

更新on子句中引用的列。

 

--实验
JOHN@ ora10g> select * from jobs;

JOB_ID     JOB_TITLE    MIN_SALARY MAX_SALARY
---------- ------------ ---------- ----------
ad_pres    vp                20000      40000
fi_account accountant         4200       9000
st_clerk   stock clerk        2000       5000
it_prog    programmer         4000      10000
dba        db admin           4200       9000


JOHN@ ora10g> select * from jobs_acquisition;

JOB_ID     JOB_TITLE    MIN_SALARY MAX_SALARY
---------- ------------ ---------- ----------
ad_pres    vp                10000      40000
dba        db admin           4200       9000


JOHN@ ora10g> merge into jobs j
  2  using (select * from jobs_acquisition) a
  3  on (j.job_id=a.job_id)
  4  when matched then
  5  update set
  6  j.job_title=a.job_title,
  7  j.min_salary=a.min_salary,
  8  j.max_salary=a.max_salary
  9  when not matched then
 10  insert (j.job_id, j.job_title, j.min_salary, j.max_salary)
 11  values (a.job_id, a.job_title, a.min_salary, a.max_salary);


JOHN@ ora10g> select * from jobs;

JOB_ID     JOB_TITLE    MIN_SALARY MAX_SALARY
---------- ------------ ---------- ----------
ad_pres    vp                10000      40000
fi_account accountant         4200       9000
st_clerk   stock clerk        2000       5000
it_prog    programmer         4000      10000
dba        db admin           4200       9000


JOHN@ ora10g> select * from jobs_acquisition;

JOB_ID     JOB_TITLE    MIN_SALARY MAX_SALARY
---------- ------------ ---------- ----------
ad_pres    vp                10000      40000
dba        db admin           4200       9000

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27633655/viewspace-1080661/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle 获取ddl语句
请登录后发表评论 登录
全部评论

注册时间:2012-10-23

  • 博文量
    94
  • 访问量
    249069