DanBrother的部落格

Oracle "Merge Into" SQL Statement Example

-- create an "employee" table
create table employee
(employee_id number(5) primary key,
 first_name varchar2(20),
 last_name varchar2(20),
 dept_no number(2),
 salary number(10)
);

-- Insert Data with INSERT ALL statement
insert all
into employee(employee_id,first_name,last_name,dept_no,salary)
values(1,'John','Ahn',10,50000)
into employee(employee_id,first_name,last_name,dept_no,salary)
values(2,'Bob','Doc',20,70000)
into employee(employee_id,first_name,last_name,dept_no,salary)
values(3,'Kathy','Morm',20,80000)
into employee(employee_id,first_name,last_name,dept_no,salary)
values(4,'Ray','Segh',20,60000)
into employee(employee_id,first_name,last_name,dept_no,salary)
values(5,'Bill','Kan',20,30000)
into employee(employee_id,first_name,last_name,dept_no,salary)
values(6,'Dick','Fain',20,40000)
into employee(employee_id,first_name,last_name,dept_no,salary)
values(7,'Mark','Adam',20,80000)
into employee(employee_id,first_name,last_name,dept_no,salary)
values(8,'Tim','Bird',30,60000)
select * from dual;

commit;

-- create a "bonuses" table
create table bonuses
(employee_id number primary key,
bonus number default 50
);

-- Insert Data with INSERT ALL statement
insert all
into bonuses(employee_id)
values(1)
into bonuses(employee_id)
values(2)
into bonuses(employee_id)
values(4)
into bonuses(employee_id)
values(6)
into bonuses(employee_id)
values(7)
into bonuses(employee_id)
values(8)
select * from dual;

commit;


select * from employee;
>>

select * from bonuses;
>>

Execute "Merge Into" SQL Statement:

merge into bonuses b
using (
select employee_id,salary,dept_no
from employee
where dept_no = 20) e
on (b.employee_id = e.employee_id)
when matched then
update set b.bonus = e.salary * 0.2
delete where (e.salary when not matched then
insert (b.employee_id,bonus)
values(e.employee_id,e.salary *0.1)
where (e.salary > 50000);

commit;

select * from bonuses
order by employee_id;
>>