MERGE in SQL

Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. 

• IT ENABLES US TO CONDITIONALLY UPDATE OR INSERT DATA INTO A TARGET TABLE.
• FROM 10g MERGE CAN NOW UPDATE,DELETE AND INSERT WITH SEPARATE  CONDITIONS FOR EACH.
• IT ALSO SUPPORTS UPDATE-ONLY OR INSERT-ONLY OPERATIONS.

Create Below source and target table

Source Table:

SQL>CREATE TABLE SRC  AS SELECT OBJECT_ID,OBJECT_NAME,OBJECT_TYPE  FROM ALL_OBJECTS  WHERE ROWNUM<=100;

Target Table:

SQL> CREATE TABLE TGT AS SELECT * FROM SRC WHERE ROWNUM<=20;

Simple MARGE Example:

SQL> MERGE INTO TGT  USING SRC  ON(SRC.OBJECT_ID=TGT.OBJECT_ID)  WHEN MATCHED THEN  UPDATE SET TGT.OBJECT_NAME=LOWER(SRC.OBJECT_NAME)  WHEN NOT MATCHED THEN INSERT(TGT.OBJECT_ID,TGT.OBJECT_NAME) VALUES(SRC.OBJECT_ID,SRC.OBJECT_NAME);

• FROM 10g MERGE can do UPDATE,DELETE and INSERT with separate conditions for each.

Update Using Merge:

SQL> MERGE INTO TGT  USING SRC  ON(SRC.OBJECT_ID=TGT.OBJECT_ID) WHEN  MATCHED THEN UPDATE SET TGT.OBJECT_NAME=LOWER(SRC.OBJECT_NAME);

Insert Using Merge:

SQL> MERGE INTO TGT  USING SRC  ON(SRC.OBJECT_ID=TGT.OBJECT_ID)  WHEN  NOT MATCHED THEN  INSERT(TGT.OBJECT_ID,TGT.OBJECT_NAME) VALUES(SRC.OBJECT_ID,SRC.OBJECT_NAME);

Conditional DML Using Merge:

SQL> MERGE INTO TGT  USING SRC  ON(SRC.OBJECT_ID=TGT.OBJECT_ID)  WHEN MATCHED THEN  UPDATE SET TGT.OBJECT_NAME=LOWER(SRC.OBJECT_NAME)  WHERE SRC.OBJECT_TYPE='SYNONYM'  WHEN NOT MATCHED THEN  INSERT(TGT.OBJECT_ID,TGT.OBJECT_NAME) VALUES(SRC.OBJECT_ID,SRC.OBJECT_NAME) WHERE SRC.OBJECT_TYPE='VIEW';

Deleting During Merge:

SQL> MERGE INTO TGT  USING SRC  ON(SRC.OBJECT_ID=TGT.OBJECT_ID) WHEN MATCHED THEN  UPDATE SET TGT.OBJECT_NAME=LOWER(SRC.OBJECT_NAME)  DELETE WHERE SRC.OBJECT_TYPE='SYNONYM'  WHEN NOT MATCHED THEN  INSERT(TGT.OBJECT_ID,TGT.OBJECT_NAME)VALUES(SRC.OBJECT_ID,SRC.OBJECT_NAME) WHERE SRC.OBJECT_TYPE='VIEW';

• The delete works against condition on the data, not the source target.
• Delete works only on rows that have been updated as a result of the merge. any rows in the target table that are not  touched by the merge are not deleted, even if they satisfy the delete criteria.