ITPub博客

首页 > 数据库 > Oracle > oracle9i merge 用法和例子

oracle9i merge 用法和例子

原创 Oracle 作者:coldwater 时间:2005-03-27 19:41:18 0 删除 编辑
manipulation data


  -- merge into table_name table_alias
  -- using (table|view|sub_query) alias
  -- on (join cnodition)
  -- when matched then
  -- update set
  -- col1 =col_val1,
  -- col2 = col_val2,
  -- when not matched then
  -- insert (column_list)
  -- values(column_values);
  -- Example of Merging Rows
 
 
 
  create table copy_emp as select * from employees
  where 1=0;
 
  select * from copy_emp;
 
     merge into copy_emp 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);
                        
 [@more@]

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

请登录后发表评论 登录
全部评论
  • 博文量
    13
  • 访问量
    69977