create table bonuses (employee_id number, bonus number default 100);
insert into bonuses(employee_id)
(select e.employee_id from employees e, orders o
where e.employee_id = o.sales_rep_id
group by e.employee_id);
select * from bonuses;
employee_id bonus
----------- ----------
153 100
154 100
155 100
156 100
158 100
159 100
160 100
161 100
163 100
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.01);
employee_id bonus
----------- ----------
153 180
154 175
155 170
156 200
158 190
159 180
160 175
161 170
163 195
157 95
145 14
170 96
179 62
152 90
169 100
在ibatis应用中的一个例子:
MERGE INTO ADMIN_J230_DEPT DEPT
USING (
SELECT #deptCd# AS DEPT_CD , #deptNm# AS DEPT_NM , #visibleYn# AS VISIBLE_YN
FROM DUAL
)DT
ON ( DEPT.DEPT_CD = DT.DEPT_CD )
WHEN MATCHED THEN
UPDATE SET DEPT.DEPT_NM = DT.DEPT_NM, DEPT.VISIBLE_YN = DT.VISIBLE_YN
WHEN NOT MATCHED THEN
INSERT ( DEPT_CD, DEPT_NM, VISIBLE_YN )
VALUES ( DT.DEPT_CD, DT.DEPT_NM, DT.VISIBLE_YN )