How To Use MERGE Statement In Oracle

Oracle Merge Statement allows to use more than one source and execute different operations in the same statement. Using Oracle Merge you can do Insert, Delete and Update and all in one statement. Since the Merge statement is deterministic it cannot update the same line more than 1 time. The syntax of Oracle Merge is following:

MERGE INTO <oracle_object>
   USING (your_select_statement)
   ON (your_join_condition)
   [ WHEN MATCHED THEN UPDATE SET <your_update_condition> ]
   [  DELETE WHERE (<your_delete_condition>) ]
   [ WHEN NOT MATCHED THEN INSERT (<object_columns_for_insert>)
     VALUES (<values_for_insert>)
     WHERE (<your_insert_condition>) ];

To demonstrate the Merge statements we need a table where we could amend the data. To keep this example as simple as possible we are creating a table from the Select statement. The new table will have 3 columns. The first column is ID and it contains unique numbers. The second column name is REPEATING_NUMBERS and as the name says we are going to repeat three numbers (0, 1, 2) over all lines in the same order. The third column is MY_TEXT and it contains text “Oracle Merge” with the ID number.

 CREATE TABLE my_merge_example AS
 SELECT rownum AS ID,
    mod (rownum,3) AS repeating_numbers,
    'Oracle Merge '||rownum AS my_text
   FROM dual
    CONNECT BY rownum < 11;

With the creation from the select query the table also got lines inside and the lines are following.

 SELECT *
   FROM my_merge_example mme
  ORDER BY mme.id;

Now before running the Oracle Merge statement we need to take a look at the SQL query that is used to do update, delete and insert in the MY_MERGE_EXAMPLE table. The query has the same three columns as the table but the ID value doesn’t start from 1 it does from 5 and it ends with 11th so we wouldn’t have exact match of line ID values. The REPEATING_NUMBERS has five repeating numbers – 0,1,2,3 and 4. The MY_TEXT column works the in same way as in the table and the values wouldn’t match because the difference in ID value. Take a look at the lines below.

 SELECT rownum + 4 AS ID,
    MOD (rownum,5) AS repeating_numbers,
    'New text '|| TO_CHAR(rownum + 4) AS my_text
   FROM dual CONNECT BY rownum < 11;

We had a look at the table data and at the Select statement output, so we are ready to run the Oracle Merge statement. The statement may look quite complicated but lets see the most important parts to make it more clear. We are joining the MY_MERGE_EXAMPLE table with the SQL query by using ID values (mme.id = qry.id). To update all matching lines the statement is using the same ID condition and all ID lines who has a match will be updated with columns MY_TEXT (mme.my_text = mme.my_text ||’-‘|| qry.my_text) and REPEATING_NUMBERS (mme.repeating_numbers = mme.repeating_numbers + qry.repeating_numbers). Also we will delete all lines from the MY_MERGE_EXAMPLE table who has matching ID and in SQL query the REPEATING_NUMBERS are greater than 2 (qry.repeating_numbers > 2). If you take a look at the output the Merge statement will find only two lines with this condition ID 7 and 8. Since we do know the maximum ID in the MY_MERGE_EXAMPLE table it is 10 so we can write the Oracle merge statement a bit faster and set the condition of ID in it – insert all lines from the SQL query that have ID value greater than 10 (qry.ID > 10).

 MERGE INTO my_merge_example mme
   USING ( SELECT rownum + 4 AS ID,
                  MOD (rownum,5) AS repeating_numbers,
                 'New text '|| TO_CHAR(rownum + 4) AS my_text
             FROM dual CONNECT BY rownum < 11) qry
      ON (mme.id = qry.id)
   WHEN MATCHED THEN
          UPDATE SET mme.my_text = mme.my_text ||'-'|| qry.my_text,
          mme.repeating_numbers = mme.repeating_numbers + qry.repeating_numbers
     DELETE WHERE (qry.repeating_numbers > 2)
   WHEN NOT MATCHED THEN INSERT (mme.id, mme.repeating_numbers, mme.my_text)
     VALUES (qry.id, qry.repeating_numbers, qry.my_text)
     WHERE (qry.ID > 10);

And here is the final output after running the Merge statement. As you see the MY_TEXT and REPEATING_NUMBERS are updated for IDs 5 to 10. Lines with ID 7 and 8 are delete and we got new lines from 11 to 14th. This all has been done within the one statement.

  SELECT *
   FROM my_merge_example mme
  ORDER BY mme.id;

The Oracle Merge gives you an option to execute more than one DML statement but our Oracle DBA still suggests when the condition goes too complicate it would be better to do it as separate Insert, Delete and Update statements.

See Also:
Oracle Home