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?

@jWeaver i'm facing a different problem than the one in the post you mentioned. My problem is that i can't make sure the ON (A.ID = B.EMP_CODE) returns only one row. — DylanW80, Jun 19 '15 at 14:53
would you be able to share sample data from your both table ? — Ravi, Jun 19 '15 at 14:57
@jWeaver I understand how this may seem uncooperative of me, but i really can't. Is there any other way i can help clarify the problem? — DylanW80, Jun 19 '15 at 15:00
@jWeaver I've tried the first solution proposed in the post you mentioned but now it throws "SQL Error: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table" — DylanW80, Jun 19 '15 at 15:14
Table2 contains more than 1 rows for the same emp_id - for example: (emp_code=20, days_off=30) and (emp_code=20, days_off=70). And Oracle doesn't know which one `days_off` value should choose to update records with id=20 in table1 - 30 or maybe 70 ? — krokodilko, Jun 19 '15 at 15:18

score 1 · Answer 1 · answered Jun 19 '15 at 15:06

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.

Ravi · Accepted Answer · 2015-06-19T15:25:16.397

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;

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

2 Answers2