The MERGE statement in Oracle Database is a powerful Data Manipulation Language (DML) statement that allows you to perform INSERT, UPDATE, and DELETE operations in a single statement based on specified conditions.

This table and sample data is used in the examples below:

CREATE TABLE TABLE_MERGE
(
ID1 NUMBER(2,0)
, CODE VARCHAR(50)
);

INSERT INTO table_merge (id1, code) VALUES (11, 'A_11');
INSERT INTO table_merge (id1, code) VALUES (12, 'A_12');
INSERT INTO table_merge (id1, code) VALUES (13, 'A_13');
INSERT INTO table_merge (id1, code) VALUES (14, 'A_14');
INSERT INTO table_merge (id1, code) VALUES (15, 'A_15');
INSERT INTO table_merge (id1, code) VALUES (16, 'A_16');
INSERT INTO table_merge (id1, code) VALUES (17, 'A_17');

The MERGE statement compares rows in a target table with rows from a source query or table. Based on the comparison results, it can perform different actions using WHEN MATCHED and WHEN NOT MATCHED clauses. The basic syntax structure is:

MERGE INTO target_table USING source_query ON (join_condition)
WHEN MATCHED THEN [UPDATE/DELETE operations]
WHEN NOT MATCHED THEN [INSERT operations]

The purpose of using MERGE statements is to be able to use a query to add, modify, and delete rows from a table based on conditions defined in the query. This eliminates the need for separate INSERT, UPDATE, and DELETE statements and ensures atomic execution of all operations.

Example:

MERGE INTO table_merge a
USING
(
    SELECT id1, 1 AS flag FROM table_merge WHERE id1 IN (12,13)

    UNION ALL

    SELECT 18 as id1, 1 AS flag FROM DUAL

    UNION ALL

    SELECT id1, 0 AS flag FROM table_merge WHERE id1 IN (14,15)
) b
ON (b.id1 = a.id1)
WHEN MATCHED THEN
    UPDATE SET a.code = a.code || '-MATCHED'
    DELETE WHERE b.flag != 1
WHEN NOT MATCHED THEN
    INSERT (a.id1, a.code)
    VALUES (b.id1, 'NOT MATCHED');

In this example:

• Rows with id1 IN (12,13) will be updated with '-MATCHED' suffix.
  
• Rows with id1 IN (14,15) will be deleted (flag = 0).
  
• A new row with id1 = 18 will be inserted.
  
• Rows with id1 IN (11,16,17) remain unchanged.
  

Select data from the "table_merge" table:

SELECT id1, code FROM table_merge;

Query result:

id1 code
----- ----
11 A_11
12 A_12-MATCHED
13 A_13-MATCHED
16 A_16
17 A_17
18 NOT MATCHED

The following rows were deleted:

id1 code
----- ----
14 A_14
15 A_15

One of the most common errors encountered when using MERGE statements is ORA-30926: "unable to get a stable set of rows in the source tables".

This error occurs if you try to modify a column multiple times; the table or subquery returns multiple rows that match the ON condition. Oracle requires that each target row can be matched with at most one source row to maintain data consistency.

MERGE INTO table_merge a
USING
(
    SELECT 11 AS id1, 'NEW_CODE_1' AS code FROM DUAL

    UNION ALL

    SELECT 11 AS id1, 'NEW_CODE_2' AS code FROM DUAL
) b
ON (b.id1 = a.id1)
WHEN MATCHED THEN
    UPDATE SET a.code = b.code;

Query result:

Error starting at line 1 in command:

Error report:
SQL Error: ORA-30926: unable to get a stable set of rows in the source tables
*Cause: A stable set of rows could not be obtained because of large DML activity or a non-deterministic WHERE clause.
*Action: Remove any non-deterministic WHERE clauses and reissue the DML.

Cause of the error: the query first modifies the code (id1=11) to 'NEW_CODE_1', then modifies it again to 'NEW_CODE_2'. Oracle cannot determine which value should be the final result.