I'm in the process of changing how we load client data into our web site database (v188.8.131.524). 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
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:
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.
answered 01 Apr '10, 23:12