Example 1
Merge on the same table
merge into dummy_table b
using (select 'Raj' name,0 salary from dual) a
on (a.name=b.name)
when matched then
update set b.salary=3000
when not matched then
insert (b.name,b.salary) values ('Vamsi',100000);
-- Example 2
MERGE INTO rff_consolidation_report D
USING (SELECT 1 from dual) S
ON (D.circle_ssa_key = 339 and D.month='FEB' and D.year=2012)
WHEN MATCHED THEN UPDATE SET SSA_HEAD='GM-FEB',DESIGNATION='GM',SINCE_WHEN=sysdate
WHEN NOT MATCHED THEN
INSERT (d.circle_ssa_key, d.current_revenue, d.month, d.year, d.trans_date, d.ssa_head, d.designation, d.since_when, d.updated_on)
VALUES (339, 0, 'FEB', 2012, sysdate, 'GM-gm', 'GM', sysdate, sysdate);
-- Example 3
merge into rmy_bill_master d
using
(
select pm.* from rmy_pbill_master pm , rmy_bill_master bm, rmy_portal_customers pc
where pm.unique_id_no=pc.uniqueid
and pm.unique_id_no=bm.unique_id_no(+)
and
(
pm.bill_status<>nvl(bm.bill_status,0) or
pm.pay_by_date<>nvl(bm.pay_by_date,'01-jan-4000')
)
) s
on ( d.unique_id_no=s.unique_id_no)
when matched then
update set
d.bill_status=s.bill_status,
d.pay_by_date=s.pay_by_date
when not matched then
insert
(unique_id_no,bill_status,pay_by_date)
values
(s.unique_id_no,s.bill_status,s.pay_by_date)
/
Tags:
Oracle