Oracle写merge语录,oracle中MERGE语句

MERGE是用来一次操作实现INSERT,UPDATE,DELETE的复合语句。

语法：

MERGE INTO TABLE/VIEW USING (TABLE/VIEW/SUBQUERY) ON (condition)

[WHEN METCHED THEN UPDATE SET COLUMN=expr/DEFAULT [WHERE condition]

[DELETE  WHERE condition]]

[WHEN NOT MATCHED THEN INSERT [(column,...)] VALUES(v,...) [WHERE condition]]

[LOG ERRORS [ INTO TABLE (...)] [REJECT LIMIT num/UNLIMITED]]

说明：

1、更新视图时不能使用DEFAULT值。

2、不能更新ON中的关联字段。

3、MERGE INTO的视图必须是可更新视图，参看http://my.oschina.net/hulubo/blog/53570中视图的限制。

例子：

MERGE INTO bonuses D

USING (SELECT employee_id, salary, department_id FROM employees

WHERE department_id = 80) S

ON (D.employee_id = S.employee_id)

WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01

DELETE WHERE (S.salary > 8000)

WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)

VALUES (S.employee_id, S.salary*0.1)

WHERE (S.salary <= 8000);

在update语句中，通常只能update一张表，对于多表关联更新，update语句限制关联字段必须有惟一索引。

例如：

update (select a.city_name,b.city_name as new_name

from customers a,

tmp_cust_city b

where b.customer_id=a.customer_id

)

set city_name=new_name

现在，可以使用merge来实现这个功能：

MERGE INTO a USING b ON (b.customer_id=a.customer_id )

WHEN MATCHED THEN UPDATE SET a.city_name=b.city_name