Friday, November 21, 2014

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

The reason of this error is source table. In merge queries we have source table and target table. We make some operations (Update, Insert, Delete) according to source table. We connect target table and source table in ON clause of Merge. And merge expects that source table returns unique values according to ON clause. If your columns, that you use in the ON clause, don’t provide unique key feature, you will get this error too.. Don’t forget primary key is a unique key;)
I think this is reasonable expectation. Think about it: what happens if source table returns non-unique values? Which data should be used to update rows? How can Oracle select this? So this is why Oracle expect uniqueness in On clause.
Let me make you see this error with example:
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;
In above queries we just simply create sample data. 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;
We get the error! We used col1 column for merge operation. And in our source table col1 is not unique. We have two different rows in source_table that col1 column is '1'. So our merge query couldn’t decide the which one has desirable value.(1,'a','w') OR (1,'b',x')? You couldn’t decide it too, didn’t you:)
Now let’s make correct this merge query. I’m looking rows and as I see col1 AND col2 provide unique values together. If we redesign our query and make the join on these values we will successfully make the operations.
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;
In the final target table our values will be like this:



No comments:

Post a Comment