MEGE Sentence
This oracle tutorial explains how to use the oralce MEGER sentence with syntax and samples.
Description
The MEGE sentence aim to reduce operator sentences.
Syntax
The syntax for the MEGE sentence is
MEGE <hint> INTO <TABLE_NAME> USING <table_view_or_query> ON (<condition>) WHERE MATCH THEN <update_clause> DELETE <where_clause> WHERE NOT MATCHED THEN <insert_clause> [LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];
Example
CREATE TABLE dept60_bonuses(
employee_id NUMBER,
bouns_amt NUMBER
);
INSERT INTO dept60_bonuses VALUES(103,0);
INSERT INTO dept60_bonuses VALUES(104,0);
INSERT INTO dept60_bonuses VALUES(105,0);
commit;
SELECT employee_id,last_name,salary FROM employees WHERE department_id=60;
MERGE INTO dept60_bonuses b
USING(
SELECT employee_id,salary,department_id
FROM employees
WHERE department_id=60) e
ON (b.employee_id=e.employee_id)
WHEN MATCHED THEN
UPDATE SET b.bouns_amt=e.salary*2
WHERE b.bouns_amt=0
DELETE WHERE (e.salary>7500)
WHEN NOT MATCHED THEN
INSERT (b.employee_id,b.bouns_amt)
VALUES (e.employee_id,e.salary*0.1)
WHERE (e.salary<7500);