I'm in the process of changing how we load client data into our web site database (v11.0.1.2044). We use staging tables (zz prefix) for the initial load. After that, I use the MERGE feature to insert new rows or update existing one. The client data may not contain rows that were previously inserted (i.e. the record was deleted in the client database). To deal with this, I first delete rows in the master table that no longer exist in the staging table. In the end, I am left with the following:

//delete rows not present in new data set
DELETE FROM fd_master 
WHERE fd_master.fd_entity = '0100'
  AND fd_master.fd_id NOT IN (SELECT f.fd_id 
                                   FROM zz_fd_master f
                               WHERE f.fd_entity = '0100') ;
COMMIT;

//insert and update funds
MERGE INTO fd_master
   USING zz_fd_master ON fd_master.fd_entity = zz_fd_master.fd_entity
                         AND fd_master.fd_id = zz_fd_master.fd_id 
                         AND zz_fd_master.fd_entity = '0100'
   WHEN NOT MATCHED THEN INSERT
   WHEN MATCHED THEN UPDATE;
COMMIT;

Is there a way to use MERGE so that I could combine these two statements? Thanks, Tom

asked 01 Apr '10, 19:37

Tom%20Rolseth's gravatar image

Tom Rolseth
191778
accept rate: 0%

edited 03 Apr '10, 10:18

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659


The MERGE statement can not modify rows in the target table that do not match any row in the source query - it can only insert new rows or update matching rows from the source table - and therefore you will not be able to delete rows from your target (aka master) table using MERGE directly.

If you really don't want to run two updates (delete followed by merge) and want to use a single merge statement, then you will need to construct a source table that consist of all rows that you wish to insert/update/delete. This can be done using a UNION ALL.

E.g. something like:

MERGE
 INTO fd_master ( _col-list_ ) as ttable
USING ( select 'new' as zz_src, * 
          from zz_fd_master
         union all
        ( select 'old' as zz_src, *
            from fd_master
           where fd_id NOT IN (
                  SELECT f.fd_id
                    FROM zz_fd_master f
                   WHERE f.fd_entity = '0100')
        )
      ) as stable
   ON ttable.fd_id = stable.fd_id

-- rows that exists in old but not in new   
 WHEN MATCHED AND stable.zz_src = 'old'
 THEN DELETE

-- rows that exists in old and new - update the row    
 WHEN MATCHED AND stable.zz_src = 'new'
 THEN UPDATE SET _fill-in-the-cols-that-need-updated_

-- rows that exists in new and not old - insert
 WHEN NOT MATCHED
 THEN INSERT ( _col-list_ ) values ( _col-list_ ) -- exists in old and new

You will need to fill in a few pieces according to your table schema and requirements.

I'm not sure this single statement would run any faster then running two separate queries? It will likely depend on the size of your tables.

permanent link

answered 01 Apr '10, 23:12

Mark%20Culp's gravatar image

Mark Culp
22.9k9129269
accept rate: 40%

For one thing, it moves all the deletes and insert/updates into a single transaction, from two transactions before. Question: in the original implementation, what makes MERGE more attractive than INSERT ON EXISTING UPDATE?

(02 Apr '10, 10:11) Breck Carter
1

@Breck: If all you are doing in inserting/updating rows from a source into the target table and matching primary key, then INSERT ON EXISTING UPDATE ON EXISTING UPDATE is much easier to write and read and does the job (and is what I would use), but if you are doing anything more complicated then it can't be done... so that is where MERGE is very useful. I've used MERGE in a number of applications but still fall back to INSERT ON EXISTING UPDATE for the simple cases.

(02 Apr '10, 19:53) Mark Culp

The staging tables we use (those with a zz prefix) do not have primary keys because our client data often has dupes. I don't think you can use INSERT ON EXISTING UPDATE if the table does not have a primary key.

(08 Apr '10, 21:32) Tom Rolseth

@Tom: Correct. ON EXISTING UPDATE requires a primary key.

(09 Apr '10, 02:37) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×6
×1

question asked: 01 Apr '10, 19:37

question was seen: 2,037 times

last updated: 03 Apr '10, 10:18