Oracle Merge Statement

Merge Statement

The MERGE statement was introduced in Oracle 9i , For
conditional insert or update data depending on its presence  this is sometimes called as an "upsert". 

In this blog we will learn the syntax with example and performance advantage of the command and error handling in merge command .

To start with ,
we will create two table 
1) A destination table or target table where the rows will be inserted updated or deleted 2) Source table :it can be a table or it can be a from Query joining multiple tables.
---
Semantics
INTO Clause
USING Clause
ON Clause
merge_update_clause
merge_insert_clause
error_logging_clause 

drop table test1;
CREATE TABLE test1 AS SELECT * FROM   all_objects WHERE  1=2;
select * from test1;
--With both clause
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHEN NOT MATCHED THEN
INSERT (object_id, owner,object_name,created,last_ddl_time,namespace,status)
VALUES (b.object_id, b.owner,b.object_name,b.created,b.last_ddl_time,b.namespace,b.status);
--With not matched clause (insert only)
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, owner,object_name,created,last_ddl_time,namespace,status)
VALUES (b.object_id, b.owner,b.object_name,b.created,b.last_ddl_time,b.namespace,b.status);
--With matched clause (update only)
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN  MATCHED THEN
UPDATE SET a.status = b.status;
-- Both clauses with conditional operation
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE  b.status != 'VALID'
WHEN NOT MATCHED THEN
INSERT (object_id, owner,object_name,created,last_ddl_time,namespace,status)
VALUES (b.object_id, b.owner,b.object_name,b.created,b.last_ddl_time,b.namespace,b.status)
WHERE  b.status != 'VALID';
-- No matched clause, insert only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, owner,object_name,created,last_ddl_time,namespace,status)
VALUES (b.object_id, b.owner,b.object_name,b.created,b.last_ddl_time,b.namespace,b.status)
WHERE  b.status != 'VALID';
-- With matched clause, update only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE  b.status != 'VALID';
---Delete clause
CREATE TABLE source AS SELECT level AS id,CASE WHEN MOD(level, 2) = 0 THEN 10
                                               ELSE 20
                                               END AS status,
'Description of level ' || level AS description
FROM dual CONNECT BY level <= 5;
select * from source;
CREATE TABLE destination AS SELECT level AS id,CASE WHEN MOD(level, 2) = 0 THEN 10
                                                    ELSE 20
                                                    END AS status,
'Description of level ' || level AS description
FROM   dual CONNECT BY level <= 10;
select * from destination;

MERGE INTO destination d
USING source s
ON (s.id = d.id)
WHEN MATCHED THEN
UPDATE SET d.description = 'Updated'
DELETE WHERE d.status = 10;