I think Merge statement is not standard, but I was wrong. It was officially introduced in the
SQL:2003standard. 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
