Merge: Modify Multiple Tables

Question

Is it possible to modify multiple tables using the merge function in Oracle? In each case, you must perform various operations, for example:

 MERGE INTO users tab1 USING(SELECT 1 id, 10 points FROM dual) tab2 ON(tab1.id = tab2.id ) WHEN MATCHED THEN UPDATE SET points = tab2.points UPDATE "abc" = action.status -- another table WHEN NOT MATCHED THEN INSERT(id, points) VALUES(tab2.id, tab2.points) UPDATE "def" = action.status -- another table 

Answer 1

According to the documentation, the simple answer is no. The syntax supports a single table or view. However, with an updated view, you can add / update to multiple tables.

However, your example seems to be trying to do something else that you cannot do. The WHEN MATCHED clause indicates what to do with UPDATE. You cannot INSERT from this sentence. The same goes for the WHEN NOT MATCHED clause - you cannot UPDATE from this clause, only INSERT.