Reputation: 15
I can not find this information in documentation, also cant find any information about possibility this operation in other internet resources.
Upvotes: 0
Views: 559
Reputation: 8361
No, you cannot use MERGE
to change two or more tables in one operation.
The syntax of MERGE
in the documentation has only one bubble for a table or a view. Theoretically, a view could consist of more than one table, but MERGE
will not work on such a view.
EDIT: No, MERGE
on a view that joins more than one table doesn't work:
CREATE TABLE t1 (id NUMBER PRIMARY KEY, c1 VARCHAR2(30));
CREATE TABLE t2 (id NUMBER PRIMARY KEY REFERENCES t1(id), c2 VARCHAR2(30));
CREATE TABLE m (id NUMBER PRIMARY KEY);
CREATE VIEW v12 AS SELECT t1.id, t1.c1, t2.c2 FROM t1 JOIN t2 ON t1.id=t2.id;
INSERT INTO t1 VALUES(1, 'BEFORE');
INSERT INTO t2 VALUES(1, 'before');
INSERT INTO m VALUES(1);
SELECT * FROM v12;
ID C1 C2
1 BEFORE before
`MERGE` causes an error:
MERGE INTO v12 USING m ON (v12.id = m.id)
WHEN MATCHED THEN UPDATE set c1='AFTER';
ORA-38106: MERGE not supported on join view or view with INSTEAD OF trigger.
*Action: When using MERGE to modify a view, you must only specify a single
table in the view, and the view cannot have an INSTEAD OF trigger.
However, UPDATE
works, but only if one underlying table is affected:
UPDATE v12 SET c1='AFTER';
1 row updated.
UPDATE v12 SET c2='after';
1 row updated.
UPDATE v12 SET c1='AFTER', c2='after';
SQL Error: ORA-01776: cannot modify more than one base table through a join view
Upvotes: 1
Reputation: 50077
A MERGE statement can only have a single target, and thus can only INSERT or UPDATE a single table. It cannot be used to modify more than a single table at once, unless (as @wolφi suggests below) the target of the MERGE is a view with an INSTEAD OF trigger which then modifies multiple tables.
Upvotes: 0