CODEWITHSUNDEEP
sqloracle-databaseperformancemergesql-update
sagi
sagi

Reputation: 40491

Merge with more than one statement after WHEN MATCHED

I'm familiar with oracle's MERGE statement which is normally used for UPDATE ELSE INSERT.

My question is, could it be used to update + insert to a log table?
I want to monitor all the records that had been updated, so I need to save them in a log table. So, instead of selecting from two huge tables again, I want to place another INSERT statement inside the WHEN MATCHED clause . So it would be something like:

MERGE INTO tableA A
using TableB b
ON (a.Col = b.Col)
WHEN MATCHED THEN UPDATE SET A.col2 = b.col2
  INSERT INTO tableA_Log (key,col)
  VALUES (a.Key,b.col2) ;

Is this possible? Or do I have to have another query from both tables once again(500mil records and 40mil records).

Thanks in advance.

Upvotes: 3

Views: 5189

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

Is this possible?

No, not possible in a single MERGE statement.

From documentation, the definition of MERGE:

Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view.

That means you could select rows from multiple source tables, but update or insert into only one table at a time ie. the table you want to merge.

You could create an after update trigger on the table to insert the rows into your log table when rows are updated in the base table.

Upvotes: 3

Related Questions