sqloracle-databasedata-warehousesql-mergefact

Merge, delete when not on Source


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 |  1111 
Mary Jones |  1112 
James Jones |  1113 
Helen Jones |  1114

TARGET
Bob Jones |  1111 | Count 1
Mary Jones |  1112| Count 1
James Jones |  1113| Count 1
Helen Jones | | 1114| Count 1
Peter Market |  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.


Solution

  • You could do a merge using the target full outer joined with source, but id has to a unique key across both for this to work.

    MERGE INTO target tgt 
    USING (SELECT CASE 
                    WHEN s.id IS NULL THEN t.name --write this logic for all other non id columns.
                    ELSE s.name 
                  END     AS name, 
                  coalesce(s.id, t.id) AS id, --use target's id when no source id is available
                  CASE 
                    WHEN s.id IS NULL THEN 0 --zero for non matching ids
                    ELSE 1 
                  END      AS source_count 
           FROM   target t 
                  full outer join source s 
                               ON s.id = t.id) src 
    ON ( src.id=tgt.id) 
    WHEN matched THEN 
      UPDATE SET tgt.name = src.name, 
                 tgt.source_count = src.source_count 
    WHEN NOT matched THEN 
      INSERT (id, 
              name, 
              source_count) 
      VALUES(src.id, 
             src.name, 
             1) ; --insert 1 by default for new rows
    

    Demo