Oracle merge 语句

-- Start

假设我从别的系统收到了一个很大的文件，我把文件导入到了下面的表中。

CREATE TABLE price_work

(

id NUMBER(10, 0),

price_source VARCHAR(10),

price NUMBER(30, 6)

); 

 

现在要求你把 price_work 中的数据搞到下面的表中，如果存在则 UPDATE，否则 INSERT，怎么办？

CREATE TABLE price

(

id NUMBER(10, 0),

price_source VARCHAR(10),

price NUMBER(30, 6),

enter_date_time TIMESTAMP

);

这个问题并不难，通常，我们可以分两步，如下所示：

--更新存在的

UPDATE

price p

SET

price = (SELECT price FROM price_work WHERE id = p.id AND price_source = p.price_source),

enter_date_time = CURRENT_TIMESTAMP

WHERE

(id, price_source) IN (SELECT id, price_source FROM price_work);

---插入不存在的

INSERT INTO price

SELECT

id,

price_source,

price,

CURRENT_TIMESTAMP

FROM

price_work

WHERE

(id, price_source) NOT IN (SELECT id, price_source FROM price_work);

上面的处理是可以的，但是我们还可以有更简单的方法，就是用Merge语句，如下所示：

MERGE INTO price p

USING price_work pw

ON (p.id = pw.id AND p.price_source = pw.price_source)

WHEN MATCHED THEN UPDATE SET p.price = pw.price

WHEN NOT MATCHED THEN INSERT VALUES (pw.id, pw.price_source, pw.price, CURRENT_TIMESTAMP);

假设现在让你只 merge price_source = 'NYSE' 的行，怎么办？

MERGE INTO price p

USING (SELECT * FROM price_work WHERE price_source = 'NYSE') pw

ON (p.id = pw.id AND p.price_source = pw.price_source)

WHEN MATCHED THEN UPDATE SET p.price = pw.price

WHEN NOT MATCHED THEN INSERT VALUES (pw.id, pw.price_source, pw.price, CURRENT_TIMESTAMP);

假设现在让你 merge 那些 price > 0 的行，删除那些 price <= 0 的行，怎么办？

MERGE INTO price p

USING (SELECT * FROM price_work WHERE price_source = 'NYSE') pw

ON (p.id = pw.id AND p.price_source = pw.price_source)

WHEN MATCHED THEN

UPDATE SET p.price = pw.price WHERE pw.price > 0

DELETE WHERE pw.price <= 0

WHEN NOT MATCHED THEN

INSERT VALUES (pw.id, pw.price_source, pw.price, CURRENT_TIMESTAMP) WHERE pw.price > 0;

强大的merge语句