PL/SQL - Update multiple rows in the target table from one row in the source table

Question

I'm using a merge statement to merge two tables where one row in the source table may update multiple rows in the target table.

It goes a bit like this

MERGE TABLE1 A
USING (SELECT EMP_CODE, DAYS_OFF FROM TABLE2) B
ON (A.ID = B.EMP_CODE)
WHEN MATCHED THEN
UPDATE SET A.DAYS_OFF = B.DAYS_OFF;

However, when i attempt this, i get SQL Error: ORA-30926: unable to get a stable set of rows in the source tables

Is there any other way i can do this?

Answer 1

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

Because, your source table probably contains duplicate values.

You probably need to add one more column to uniquely identify each row.

CREATE TABLE source_table (
    col1 NUMBER,
    col2 VARCHAR2(10),
    col3 VARCHAR2(10)
);

INSERT INTO source_table (col1, col2, col3) VALUES (1, 'a', 'w');
INSERT INTO source_table (col1, col2, col3) VALUES (1, 'b', 'x');
INSERT INTO source_table (col1, col2, col3) VALUES (2, 'c', 'y');
INSERT INTO source_table (col1, col2, col3) VALUES (3, 'c', 'z');

COMMIT;

CREATE TABLE target_table (
    col1 NUMBER,
    col2 VARCHAR2(10),
    col3 VARCHAR2(10)
);

INSERT INTO target_table (col1, col2, col3) VALUES (1, 'b', 'z');
INSERT INTO target_table (col1, col2, col3) VALUES (3, 'd', 'w');

COMMIT;

Now we are going to merge two table.

MERGE INTO target_table trg
USING (--Actually we can simply write source_table for this example but I want to write Select:)
       SELECT col1, col2, col3
       FROM source_table 
      ) src 
ON (trg.col1 = src.col1)
WHEN MATCHED THEN UPDATE SET --Don't forget you cannot update columns that included in ON clause
    trg.col2 = src.col2,
    trg.col3 = src.col3
WHEN NOT MATCHED THEN INSERT
    (
        col1,
        col2,
        col3
    )
    VALUES
    (
        src.col1,
        src.col2,
        src.col3
    );

COMMIT;

Solution

MERGE INTO target_table trg
USING source_table src --Now I simply write the table name:)
ON (
    trg.col1 = src.col1 AND
    trg.col2 = src.col2
   )
WHEN MATCHED THEN UPDATE SET --Don't forget you cannot update columns that included in ON clause
    trg.col3 = src.col3
WHEN NOT MATCHED THEN INSERT
    (
        col1,
        col2,
        col3
    )
    VALUES
    (
        src.col1,
        src.col2,
        src.col3
    );

COMMIT;

Read more

Answer 2

That error means Oracle cannot get for each record in A,a set of records that only match to this one (f. ex. a set of records that leads to a many-to-many relationship).

What you need to do is to check in target table for duplicated items with the same "ID"

SQL> list                
  1  MERGE INTO  TABLE1 A
  2   USING (SELECT * FROM TABLE2) B
  3   ON (A.ID = B.EMP_CODE)
  4   WHEN MATCHED THEN
  5  UPDATE SET A.DAYS_OFF = B.DAYS_OFF
  6*
SQL> r
  1  MERGE INTO  TABLE1 A
  2   USING (SELECT * FROM TABLE2) B
  3   ON (A.ID = B.EMP_CODE)
  4   WHEN MATCHED THEN
  5  UPDATE SET A.DAYS_OFF = B.DAYS_OFF
  6*
MERGE INTO  TABLE1 A
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables

SQL> select id, count(0) from table1 group by id;

        ID   COUNT(0)
---------- ----------
         1          2

SQL> delete from table1 t1 where t1.rowid not in (select max(rowid) from table1 t2 where t2.id = t1.id);

1 row deleted.

SQL> commit;

Commit complete.

SQL> MERGE INTO  TABLE1 A
 USING (SELECT * FROM TABLE2) B
 ON (A.ID = B.EMP_CODE)
 WHEN MATCHED THEN
UPDATE SET A.DAYS_OFF = B.DAYS_OFF  2    3    4    5  ; 

2 rows merged.

SQL> commit;

Commit complete.