oracle 9i merge into,oracle9i merge 用法和例子

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/，如需转载，请注明出处，否则将追究法律责任。