from merge statement getting ORA-30926: unable to get a stable set of rows in the source tables

Question

MERGE INTO TARGET_TABLE TAR
        USING (
                  SELECT
                      DISTINCT(INP.BATCH_NUMBER),
                      INP.RECORD_NUMBER,
                      INP.ACC_NO
                  FROM
                      SOURCE_INPUT INP
                      ORDER BY INP.BATCH_NUMBER
              )MRG 
              ON ( NVL(TAR.ACC_NO,'NULL') = NVL(MRG.ACC_NO,'NULL') )
        WHEN MATCHED THEN UPDATE
        SET TAR.BATCH_NUMBER = MRG.BATCH_NUMBER,
            TAR.RECORD_NUMBER = MRG.RECORD_NUMBER;
            

i want to merge the BATCH_NUMBER and RECORD_NUMBER from SOURCE_INPUT table(both are not null) to TARGET_TABLE. IN BOTH THE TABLE ACC_NO is null for 2 ROWS and rest are having value.

so the problem is coming after doing the NULL value validation:- ON ( NVL(TAR.ACC_NO,'NULL') = NVL(MRG.ACC_NO,'NULL') ) i have used distinct , order by. but still "unable to get a stable set of rows in the source tables" error is coming.

working only for not null fields:-

MERGE INTO TARGET_TABLE TAR
        USING (
                  SELECT
                      INP.BATCH_NUMBER,
                      INP.RECORD_NUMBER,
                      INP.ACC_NO
                  FROM
                      SOURCE_INPUT INP
              )MRG 
              ON TAR.ACC_NO = MRG.ACC_NO
        WHEN MATCHED THEN UPDATE
        SET TAR.BATCH_NUMBER = MRG.BATCH_NUMBER,
            TAR.RECORD_NUMBER = MRG.RECORD_NUMBER;

i want to make it work for null fields. Tried below:-

MERGE INTO TARGET_TABLE TAR
        USING (
                  SELECT
                      DISTINCT(INP.BATCH_NUMBER),
                      INP.RECORD_NUMBER,
                      INP.ACC_NO
                  FROM
                      SOURCE_INPUT INP
                      ORDER BY INP.BATCH_NUMBER
              )MRG 
              ON ( NVL(TAR.ACC_NO,'NULL') = NVL(MRG.ACC_NO,'NULL') )
        WHEN MATCHED THEN UPDATE
        SET TAR.BATCH_NUMBER = MRG.BATCH_NUMBER,
            TAR.RECORD_NUMBER = MRG.RECORD_NUMBER;

Answer 1

Sample tables; both - as you said - have two rows with null value in acc_no column, the one you're using to join rows in both tables.

SQL> SELECT * FROM source_input;

BATCH_NUMBER RECORD_NUMBER     ACC_NO
------------ ------------- ----------
           1             1          1
           2             2
           3             3

Target table's columns should be updated to values contained in source table:

SQL> SELECT * FROM target_table;

BATCH_NUMBER RECORD_NUMBER     ACC_NO
------------ ------------- ----------
         100           100          1
         200           200
         300           300

SQL>

The way it currently is, how do you know which values should replace 200 and 300 in target_table? Is it 2, is it 3?

Your query - as you already know - doesn't work because Oracle doesn't know which rows in target table should be joined to which rows in source table:

SQL> MERGE INTO target_table tar
  2       USING (
  3                  SELECT DISTINCT inp.batch_number, inp.record_number, inp.acc_no
  4                    FROM source_input inp
  5                ORDER BY inp.batch_number
  6             ) mrg
  7          ON (NVL (tar.acc_no, -1) = NVL (mrg.acc_no, -1))
  8  WHEN MATCHED
  9  THEN
 10     UPDATE SET tar.batch_number = mrg.batch_number, tar.record_number = mrg.record_number;
MERGE INTO target_table tar
           *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables


SQL>

If it is OK to take any row which doesn't contain acc_no, use e.g. min aggregate function:

SQL> MERGE INTO target_table tar
  2       USING (  SELECT MIN (inp.batch_number) batch_number,
  3                       MIN (inp.record_number) record_number,
  4                       NVL (inp.acc_no, -1) acc_no
  5                  FROM source_input inp
  6              GROUP BY NVL (inp.acc_no, -1)) mrg
  7          ON (NVL (tar.acc_no, -1) = mrg.acc_no)
  8  WHEN MATCHED
  9  THEN
 10     UPDATE SET tar.batch_number = mrg.batch_number, tar.record_number = mrg.record_number;

3 rows merged.

SQL> SELECT * FROM target_table;

BATCH_NUMBER RECORD_NUMBER     ACC_NO
------------ ------------- ----------
           1             1          1
           2             2             --> both rows whose ACC_NO is empty
           2             2             --> got same values for other two columns

SQL>

---

There's another option which might do what you wanted, but that's PL/SQL procedure which joins rows on acc_no if it exists; if not, take some pseudo-value (such as the one returned by row_number function). Something like this:

Revert previously made changes:

SQL> rollback;

Rollback complete.

SQL> SELECT * FROM target_table;

BATCH_NUMBER RECORD_NUMBER     ACC_NO
------------ ------------- ----------
         100           100          1
         200           200
         300           300

Procedure:

SQL> BEGIN
  2     FOR cur_t
  3        IN (SELECT acc_no, ROWID rid, ROW_NUMBER () OVER (ORDER BY ROWID) rn
  4              FROM target_table)
  5     LOOP
  6        FOR cur_s
  7           IN (SELECT acc_no, batch_number, record_number
  8                 FROM (SELECT acc_no,
  9                              batch_number,
 10                              record_number,
 11                              ROW_NUMBER () OVER (ORDER BY ROWID) rn
 12                         FROM source_input)
 13                WHERE    acc_no = cur_t.acc_no
 14                      OR rn = cur_t.rn)
 15        LOOP
 16           UPDATE target_table t
 17              SET t.batch_number = cur_s.batch_number,
 18                  t.record_number = cur_s.record_number
 19            WHERE t.ROWID = cur_t.rid;
 20        END LOOP;
 21     END LOOP;
 22  END;
 23  /

PL/SQL procedure successfully completed.

which result in

SQL> SELECT * FROM target_table;

BATCH_NUMBER RECORD_NUMBER     ACC_NO
------------ ------------- ----------
           1             1          1
           2             2               --> both target rows without ACC_NO
           3             3               --> have different values from the source table

SQL>

Certainly, it will perform worse (loop in a loop, row-by-row processing) on large data sets and it depends on number of rows you have in both tables. In this example, both of them have the same number of rows. What will you do if that number differs?

There should be additional rules which explain "what if" situations.

Therefore, maybe you can't do what you wanted, maybe you can't.