如果存在就更新，不存在就插入
9i已经支持了，但是只支持select子查询，
如果是单条数据记录，可以写作select …… from dual的子查询。但是merge不允许更新on条件里面的列的值，否则会报ORA-38104错误。
语法为：
merge into table
using data_source
on (condition)
when matched then update_clause
when not matched then insert_clause;
如
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
delete where (s.salary > 8000)
when not matched then insert (d.employee_id, d.bonus)
values (s.employee_id, s.salary*0.1)
where (s.salary <= 8000);
============================
例子1：
merge into c1 a
using c2 b on (a.name = b.name and a.areaid = b.areaid)
when matched then
update set a.id= seq_c1.nextval --不用写where a.name = b.name and a.areaid = b.areaid
when not matched then
insert
(id,name,areaid,)
values
(a.id, a.name,a.areaid);
例子2：
merge into hfee a
using dual b
on (a.username = vcr_id.username and a.starttime = vcr_id.starttime)
when matched then
update
set orginfee = vcr_id.orginfee,
favorfee = vcr_id.favorfee,
fee = vcr_id.fee,
balancefee = vi_balancefee
when not matched then
insert
(username, starttime, orginfee, favorfee, fee, balancefee)
values
(vcr_id.username,
vcr_id.starttime,
nvl(vcr_id.orginfee, 0),
nvl(vcr_id.favorfee, 0),
nvl(vcr_id.fee, 0),
nvl(vi_balancefee, 0));
merge into 出现错误 ora-30926,如何解决？
这个错误是由于using子句后的表存在多条符合条件的数据，可以用distinct、group by 等做处理：
merge into tdest d
using (select srckey, sum(srcdata) sumsrc from tsrc group by srckey) s
on (d.destkey = s.srckey)
when matched then
update set d.destdata = s.sumsrc + d.destdata
when not matched then
insert (destkey, destdata) values (srckey, sumsrc);
merge into account a
using (select id, sum(balance) sum_balance from transaction group by id) t
on (a.id = t.id)
when matched then
update set a.balance = a.balance + t.sum_balance
when not matched then
insert (a.id, a.balance) values (t.id, t.sum_balance);
