CODEWITHSUNDEEP
sqloracle-databasemerge
Tgy
Tgy

Reputation: 15

Can I use MERGE statement to use insert, update and delete clauses on 2 or more tables through one operation?

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

Answers (2)

wolφi
wolφi

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

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

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

Related Questions