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