Merge, delete when not on target

Coco Beans
Coco Beans used Ask the Experts™
on
Hi All,

I have an issue with a merge statement into a FACT TABLE


It was pretty simple until the users started to delete records from the source.

Current SQL:

Set Count = 1
WHEN NOT MATCHED
   INSERT
WHEN MATCHED
 UPDATED



New SQL:

So in this example, a record has been deleted from the source, it no longer matches but there is nothing to insert.  I would like it the count to be set to 0.

WHEN DELETED FROM SOURCE
Set Count = 0



Source
Bob Jones | Confirmed | 1111
Mary Jones | Confirmed | 1112
James Jones | Confirmed | 1113
Helen Jones | Confirmed | 1114


TARGET
Bob Jones | Confirmed | 1111 | Count 1
Mary Jones | Confirmed | 1112| Count 1
James Jones | Confirmed | 1113| Count 1
Helen Jones | Confirmed | 1114| Count 1
Peter Market | Confirmed | 1115| Count 0



I’m loading to a fact table using a merge and now they are just blanket deleting records, my facts are off.  This must be accounted for somehow?


Thank you for any help at all.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
If I understand this correctly, I don't think you can do what you one in one step with a single MERGE statement.

Easiest would be to set ALL rows to a count=0, then let the MERGE set them to a 1 when there is a match.
Coco BeansDesigner

Author

Commented:
Thanks.  I don't want to clear down dimensions are some are slowing change and this would leave orphaned rows in the dimensions
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Sorry but I do not understand what that post is supposed to tell me.
Commented:
Oracle Merge statement doesn't  works for  every record in target , just works for every record in source ( like left join and not as you expected)

 So you can't set target column when correspondence rows not exist in source with MERGE statement.
you may do it with extra one step like that
UPDATE fact WHERE NOT EXIST (SELECT 1 from source WHERE condition)

Open in new window

Coco BeansDesigner

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial