Analysis on the update of the MERGE statement and multi-Table Association in oracle

MERGE is a composite statement used to implement INSERT, UPDATE, and DELETE operations at a time. Syntax: 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] Note: 1. The DEFAULT value cannot be used to update a view. 2. The associated fields in ON cannot be updated. Example: 1 merge into bonuses D2 USING (SELECT employee_id, salary, department_id FROM employees3 WHERE department_id = 80) S4 www.2cto.com ON (D. employee_id = S. employee_id) 5 when matched then update set d. bonus = D. bonus + S. salary *. 016 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); in the update statement For multi-Table associated updates, the update statement limits that the associated fields must have unique indexes. Example: 1 update (select. city_name, B. city_name as new_name2from MERs a, 3tmp_cust_city b4 www.2cto.com where B. customer_id =. customer_id5) 6 set city_name = new_name now, you can use merge to implement this function: 1 merge into a USING B ON (B. customer_id =. customer_id) 2 when matched then update set. city_name = B. city_name by hulubo