oracle中MERGE语句和多表关联的update简析
MERGE是用来一次操作实现INSERT,UPDATE,DELETE的复合语句。
语法：
MERGE INTO TABLE/VIEW USING (TABLE/VIEW/SUBQUERY) ON (condition)
[WHEN METCHED THEN UPDATE SET COLUMN=expr/DEFAULT [WHERE condition]
www.2cto.com [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中的关联字段。
例子：
1
MERGE INTO bonuses D
2
USING (SELECT employee_id, salary, department_id FROM employees
3
WHERE department_id = 80) S
4 www.2cto.com
ON (D.employee_id = S.employee_id)
5
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
6
DELETE WHERE (S.salary > 8000)
7
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
8
VALUES (S.employee_id, S.salary*0.1)
9
WHERE (S.salary <= 8000);
在update语句中，通常只能update一张表，对于多表关联更新，update语句限制关联字段必须有惟一索引。
例如：
1
update (select a.city_name,b.city_name as new_name
2
from customers a,
3
tmp_cust_city b
4 www.2cto.com
where b.customer_id=a.customer_id
5
)
6
set city_name=new_name
现在，可以使用merge来实现这个功能：
1
MERGE INTO a USING b ON (b.customer_id=a.customer_id )
2
WHEN MATCHED THEN UPDATE SET a.city_name=b.city_name
作者 hulubo
oracle中MERGE语句和多表关联的update简析 
