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:
