This tutorial is based on examples to be easier to follow. The Oracle Merge Statement allows you use more than one source and combine different operations in the same time. Using Oracle Merge you can do Insert, Delete and Update in the same SQL statement. Since the Merge statement is deterministic you cannot update the same line more than 1 time. The Oracle Merge syntax 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 continue with the Merge statement examples we would need a table whose data we will amend. To keep this example as simple as possible we are creating the table from the Select statement and the table has 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 repeating three numbers (0, 1, 2) over all lines. 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;
The table has filled with lines because we did use the Select statement in its creation and the lines are looking as following.
SELECT * FROM my_merge_example mme ORDER BY mme.id;
Now before we will run the Oracle Merge statement we need to take a look at the SQL query used to update and insert 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 matching of lines. The REPEATING_NUMBERS has five numbers repeating – 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 if the ID value. Please 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 execute the Oracle Merge statement. The statement may look quite huge but let’s see the most important parts. 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 it 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). The Merge statement found only two lines with this condition ID 7 and 8. Since we do know our maximum ID in the table is 10 so we do the insert statement a bit faster and set the condition on ID – insert all lines from the SQL query that has 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 Oracle 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 gone and we got new lines from 11 to 14th. All this has been done with one statement.
SELECT * FROM my_merge_example mme ORDER BY mme.id;
The Oracle Merge gives you an option to do more than one DML statement but Online Tech Support still thinks when the condition goes to complicate it would be better to do it as separate statements.
See Also:
Oracle Insert Oracle Update Oracle Delete Home