Merge, delete when not on target

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.
Coco BeansDesignerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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 BeansDesignerAuthor Commented:
Thanks.  I don't want to clear down dimensions are some are slowing change and this would leave orphaned rows in the dimensions
slightwv (䄆 Netminder) Commented:
Sorry but I do not understand what that post is supposed to tell me.
dutaliCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Coco BeansDesignerAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.