merge用法linux,merge用法你了解多少呢，：）

merge在实现数据抽取合并中有着很多优势,这里简单的做个介绍,所有内容都来自oracle database warhouse guide.

1、在10g之前，常用的就是这样的：

MERGE INTO sales s

USING new_sales n

ON (s.sales_transaction_id = n.sales_transaction_id)

WHEN MATCHED THEN

UPDATE

SET s.sales_quantity_sold = s.sales_quantity_sold +

n.sales_quantity_sold,

s.sales_dollar_amount = s.sales_dollar_amount +

n.sales_dollar_amount

WHEN NOT MATCHED THEN

INSERT

(sales_transaction_id, sales_quantity_sold, sales_dollar_amount)

VALUES

(n.sales_transcation_id, n.sales_quantity_sold, n.sales_dollar_amount);

2、在10g中增加了功能，就是你可以只用update或insert部分，而不必两者同时出现，同时可以在update,insert部分新增加where条件。比如类似下面的语句：

a、只有update语句：

MERGE

USING Product_Changes S

INTO Products D1

ON (D1.PROD_ID = S.PROD_ID)

WHEN MATCHED THEN

UPDATE

SET D1.PROD_STATUS = S.PROD_NEW_STATUS;

b、只有insert语句：

MERGE

USING New_Product S

INTO Products D2

ON (D2.PROD_ID = S.PROD_ID)

WHEN NOT MATCHED THEN

INSERT (PROD_ID, PROD_STATUS) VALUES (S.PROD_ID, S.PROD_NEW_STATUS)；

c、带where条件的update语句：

MERGE

USING Product_Changes S INTO Products P

ON (P.PROD_ID = S.PROD_ID)

WHEN MATCHED THEN

UPDATE

SET P.PROD_LIST_PRICE = S.PROD_NEW_PRICE

WHERE P.PROD_STATUS "OBSOLETE" ；

d、带where条件的insert语句：

MERGE

USING Product_Changes S INTO Products P

ON (P.PROD_ID = S.PROD_ID)

WHEN MATCHED THEN

UPDATE

SET P.PROD_LIST_PRICE = S.PROD_NEW_PRICE

WHERE P.PROD_STATUS "OBSOLETE"

WHEN NOT MATCHED THEN

INSERT

(PROD_ID, PROD_STATUS, PROD_LIST_PRICE)

VALUES

(S.PROD_ID, S.PROD_NEW_STATUS, S.PROD_NEW_PRICE) WHERE S.PROD_STATUS "OBSOLETE";

3、10g中增加了delete语句部分：

MERGE

USING Product_Changes S INTO Products D

ON (D.PROD_ID = S.PROD_ID)

WHEN MATCHED THEN

UPDATE

SET D.PROD_LIST_PRICE = S.PROD_NEW_PRICE,

D.PROD_STATUS = S.PROD_NEWSTATUS

DELETE WHERE (D.PROD_STATUS = "OBSOLETE")

WHEN NOT MATCHED THEN

INSERT

(PROD_ID, PROD_LIST_PRICE, PROD_STATUS)

VALUES

(S.PROD_ID, S.PROD_NEW_PRICE, S.PROD_NEW_STATUS);

4、无条件插入语句：

MERGE

USING New_Product S INTO Products P

ON (1 = 0)

WHEN NOT MATCHED THEN

INSERT (PROD_ID, PROD_STATUS) VALUES (S.PROD_ID, S.PROD_NEW_STATUS)；

5、在10g之前，如果只需要update的时候，可以采用如下实现(从askto学到的)：

CREATE TABLE alpha (

id NUMBER(2) PRIMARY KEY,

NAME VARCHAR2(100) UNIQUE,

status CHAR(1)

);

CREATE TABLE beta (

networkid NUMBER(2),

NAME VARCHAR2(100),

PRIMARY KEY (NAME, networkid)

);

INSERT INTO alpha VALUES (1, 'Jim', 'A');

INSERT INTO alpha VALUES (2, 'Eric', 'A');

INSERT INTO alpha VALUES (3, 'Ryan', 'A');

COMMIT;

INSERT INTO beta VALUES (10, 'Jim');

INSERT INTO beta VALUES (10, 'Eric');

INSERT INTO beta VALUES (20, 'Ryan');

COMMIT;

merge into alpha a

using ( select b.*

from alpha a, beta b

where networkid = 10

and a.name = b.name ) b

on ( a.name = b.name )

when matched then update set status = 'X'

when not matched then insert (id) values ( null )；