merge update oracle unable to get a stable set of rows

Question

I am trying to update a table in Oracle based on another table (inner join) but the matching fields match on more than 1 row so I get an error:

unable to get a stable set of rows

    MERGE INTO C
    USING D
    ON (C.SYSTEM = D.SYSTEM)
    WHEN MATCHED THEN
    UPDATE SET C.REF_CD = D.CODE,
               C.REF_DT = TO_DATE('12/05/2017', 'MM/DD/YYYY')
       WHERE C.CODE = '123'
       AND D.CODE IS NOT NULL
       AND C.CLOSED = 'N'
       AND C.RCVD_DT >= TO_DATE('12/01/2017', 'MM/DD/YYYY')
       AND C.RCVD_DT <= TO_DATE('12/04/2017', 'MM/DD/YYYY')
       AND SUBSTR(C.SSN,7,3) >= D.FROM
       AND SUBSTR(C.SSN,7,3) <= D.TO;

As a SELECT statement, I can pull this information using an inner join. But as a merge statement, I get the above error. SYSTEM is the only matching data in both tables. How can I rewrite the above in a way that it will not error out?

Answer 1

I am going to show what the source of this error is.
Consider the below simple example:

CREATE TABLE A_100(
  x_system int,
  val int
);

INSERT INTO a_100 values( 1, 100 );
INSERT INTO a_100 values( 2, 200 );

CREATE TABLE B_100(
  x_system int,
  val int
);

INSERT INTO b_100 values( 1, 1100 );
INSERT INTO b_100 values( 2, 2000 );
INSERT INTO b_100 values( 2, 3000 );

commit;

---

Now please consider this join:

SELECT *
FROM A_100 a
JOIN B_100 b
ON a.x_system = b.x_system AND a.x_system = 1
;

| X_SYSTEM | VAL | X_SYSTEM |  VAL |
|----------|-----|----------|------|
|        1 | 100 |        1 | 1100 |

the above query gives one unique record from the table B_100. If you use this join condition in a merge statement, the merge will run without any error:

MERGE INTO A_100 a
USING B_100 b
ON ( a.x_system = b.x_system AND a.x_system = 1)
WHEN MATCHED THEN UPDATE SET a.val = b.val
;

1 row merged. 

---

Now please consider the below join:

SELECT *
FROM A_100 a
JOIN B_100 b
ON a.x_system = b.x_system AND a.x_system = 2
;

| X_SYSTEM | VAL | X_SYSTEM |  VAL |
|----------|-----|----------|------|
|        2 | 200 |        2 | 2000 |
|        2 | 200 |        2 | 3000 | 

The above join, for one record from A_100 gives two records from B_100.

If you try to use MERGE with the above join condition you will get the following:

MERGE INTO A_100 a
USING B_100 b
ON ( a.x_system = b.x_system AND a.x_system = 2)
WHEN MATCHED THEN UPDATE SET a.val = b.val
;

Error report -
ORA-30926: unable to get a stable set of rows in the source tables

---

Oracle simply says you :

The query for one record from the left table returned two values: 2000 and 3000 from the right table.
I can not assign two values from the right table to a single scalar field of the left table, this is impossible.
Please change the join condition so that it gives only one unique record from the right table for each record in the left table