Deleting Rows With Merge

Venn diagram of deletes in a merge statement

The merge statement allows you to insert, update and delete rows in the target table in one go. This is great. However, the delete part does not work as expected. Is it a bug? No, it works exactly as documented. Nonetheless, I was not aware of this for years. Let’s take a look at this with an example.

Setup

We create a table t (target) with three rows and a table s (source) with 4 rows. To log DML events we create some after row triggers on table t.

Insert, Update, Delete via Merge

Now, we can run this script:

The merge statement applied the insert, update and delete operation in the target table t. The result in table t is what we expect.

However, when I look at the output of the DML triggers I do not like the following things:

  • The row with id 1 was updated, even if the column c1 did not change. This update is unnecessary and should be avoided, right?
  • The row with id 3 was updated and then deleted. Updating a row and then deleting it? The first update does not seem necessary, right?

Update Filter

The merge_update_clause documents an optional where_clause for the update part of a merge statement.

Let’s try that to avoid the unnecessary updates.

Good, no more unnecessary updates. But now we have a new issue. The row with id 3 is not deleted. It looks like the delete part of the merge statement is ignored.

The Fine Print

I thought this was a bug and opened a service request some days ago. The friendly and patient support engineer directed me to this excerpt of the merge_update_clause in the SQL Language Reference of the Oracle Database 19c:

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation.

So, clearly not a bug. The second sentence can be visualized as Venn diagram:

Venn diagram of deletes in a merge statement

First Update Then Delete

So we have learned that we must first update a row before we can delete it when we use a merge statement. However, we can still avoid unnecessary updates if the row does not need to be deleted.

Let’s update our script once more:

Looks good!

The update of the row with id 1 was suppressed, because the c1 column did not change. The row with id 2 was changed, a new row with id 4 was inserted and the row with id 3 is gone. We just have to live with the prior update of id 3.

Conclusion

I imagine I’m not the only one who would have expected the merge statement to behave differently. Especially after watching How to UPSERT (INSERT or UPDATE) rows with MERGE in Oracle Database by Chris Saxon. He also mentioned “delete” here and here.

Remember:

Delete only processes rows that were updated.
— Chris Saxon

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.