Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Don.

Asked: December 17, 2019 - 6:47 pm UTC

Answered by: Connor McDonald - Last updated: January 06, 2020 - 2:50 am UTC

Category: SQL - Version: 12c

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: SUM is better than DISTINCT

You Asked

How do I delete with a merge statement? I want to update MERGE_DELETE_TEST2 to match MERGE_DELETE_TEST1 (think ETL). I cannot get merge delete to remove the row that exists in MERGE_DELETE_TEST2 that does not exist in MERGE_DELETE_TEST1.


with LiveSQL Test Case:

and we said...

The DELETE in a merge is not for non-existence. From the docs

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. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted

Hence if you ETL is about update existing, insert new and delete non-existing, then the MERGE can do the first two, and then add a DELETE statement afterwards along the lines of:

delete from ...
from (col1,col2,col3) not in ( select c1,c2,c3 from ... )


and you rated our response

  (2 ratings)

Reviews

merge delete

December 18, 2019 - 12:50 pm UTC

Reviewer: Mikhail Velikikh from Ireland

>> Hence if you ETL is about update existing, insert new and
>> delete non-existing, then the MERGE can do the first two, and
>> then add a DELETE statement afterwards along the lines of:

There is also a technique which I use quite extensively in similar tasks.
I must have learnt about it from Stew Ashton: https://asktom.oracle.com/pls/apex/asktom.search?tag=effective-way-to-comparemerge-large-tables#9534275800346850951
Sometimes it is more efficient to go through the input row-source only once, so that Stew's method really makes the difference.

Connor McDonald

Followup  

January 06, 2020 - 2:50 am UTC

Good input

December 18, 2019 - 3:10 pm UTC

Reviewer: Don Simpson from Tucson, AZ, USA

That's what I was coming around to understanding. I was just hoping that I could squeeze it all into a single statement. I suppose I could by doing an un-needed update on the rows I want to delete.

It's curious that the oracle engine has to go through the effort of updating a row that it's just going to delete.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.