'merge in oracle 9i and 10g'에 해당되는 글 1건

  1. 2009.04.25 MERGE INTO Statement

MERGE INTO Statement

DB 2009.04.25 15:16
I think Merge statement is not standard, but I was wrong.  It was officially introduced in the SQL:2003 standard. In mysql, there is no statement like merge, but have similar statement "replace".

Let's look at merge statement and stduy.
(Reference :http://en.wikipedia.org/wiki/Merge_(SQL)) - until the oracle 9i
MERGE INTO table_name1 USING table_name2 ON (condition
  WHEN MATCHED THEN
  UPDATE SET column1 = value1 [, column2 = value2 ...]
  WHEN NOT MATCHED THEN
  INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])


If a record exist, you want to update it, and if the record does not exist, you want to insert the record. To do this, merge statement is introduced.


In oracle show examples.  (http://www.oracle.com/technology/products/oracle9i/daily/Aug24.html)

Without Merge statement, it will be shown belows. It is used to use in Oracle 8.

UPDATE
     (SELECT
        S.TIME_ID ,S.STORE_ID ,S.REGION_ID,
        S.PARTS s_parts ,S.SALES_AMT s_sales_amt ,S.TAX_AMT s_tax_amt ,S.DISCOUNT s_discount,
        D.PARTS d_parts ,D.SALES_AMT d_sales_amt ,D.TAX_AMT d_tax_amt ,D.DISCOUNT d_discount
     FROM SALES_JUL01 S, SALES_FACT D
     WHERE D.TIME_ID = S.TIME_ID
      AND D.STORE_ID = S.STORE_ID
      AND D.REGION_ID = S.REGION_ID)
JV
       SET d_parts = d_parts + s_parts,
          d_sales_amt = d_sales_amt + s_sales_amt,
          d_tax_amt = d_tax_amt + s_tax_amt,
          d_discount = d_discount + s_discount
;
INSERT INTO SALES_FACT (
     TIME_ID,STORE_ID ,REGION_ID,
     PARTS ,SALES_AMT ,TAX_AMT ,DISCOUNT)
     SELECT
       S.TIME_ID ,S.STORE_ID ,S.REGION_ID,
       S.PARTS ,S.SALES_AMT ,S.TAX_AMT ,S.DISCOUNT
     FROM SALES_JUL01 S
     WHERE (S.TIME_ID, S.STORE_ID, S.REGION_ID) NOT IN (
       SELECT        D.TIME_ID, D.STORE_ID, D.REGION_ID
       FROM SALES_FACT D
       )
     ;

It is very long sentences and verbosely.
So, in Oracle 9 introduce Merge statement. Upper statements changed to belows in ussing merge.

MERGE INTO SALES_FACT D
     USING SALES_JUL01 S
     ON (D.TIME_ID = S.TIME_ID
        AND D.STORE_ID = S.STORE_ID
        AND D.REGION_ID = S.REGION_ID)
     WHEN MATCHED THEN
    UPDATE
     SET d_parts = d_parts + s_parts,
          d_sales_amt = d_sales_amt + s_sales_amt,
          d_tax_amt = d_tax_amt + s_tax_amt,
          d_discount = d_discount + s_discount
      WHEN NOT MATCHED THEN
     INSERT (D.TIME_ID ,D.STORE_ID ,D.REGION_ID,
        D.PARTS ,D.SALES_AMT ,D.TAX_AMT ,D.DISCOUNT)
     VALUES (
        S.TIME_ID ,S.STORE_ID ,S.REGION_ID,
        S.PARTS ,S.SALES_AMT ,S.TAX_AMT ,S.DISCOUNT);


It is very simple, easy to undestand.

There is advancements and enhancements on the merge statement in Oracle 10g.

1) Optional insert and update

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status;



2) Conditional clause
After to update or delete statement after WHEN MATCHED THEN or WHEN NOT  MATCHED THEN  clause   can have where clauses.

With Oracle 10g, we can can now apply additional conditions(WHERE) to the UPDATE or INSERT operation within a MERGE. It is extremely useful if we have different rules for when a record is updated or inserted but we do not wish to restrict the ON condition that joins source and target together.
-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID';


3) Deleting  during merging

You can delete conditionally DELETE rows during an UPDATE operation.

MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  DELETE WHERE (b.status = 'VALID');


* examples are referenced on http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php

Posted by 김용환 '김용환'