MERGE 

 1 -- MERGE 按条件插入或更新语句。经常被用来在数据仓库中移动大量数据 避免了使用多个 INSERT UPDATE DELETE
 2 
 3 --案例
 4 MERGE INTO dept60_bonuses b
 5 USING (
 6     SELECT employee_id,salary,department_id
 7     FROM employees
 8     WHERE department_id = 60
 9 ) e
10 ON (b.employee_id = e.employee_id)
11 
12 WHEN MATCHED THEN
13 UPDATE SET b.bonus_amt = e.salary*0.2
14 WHERE b.bonus_amt = 0
15 
16 DELETE WHERE (e.salary>7500)
17 
18 WHEN NOT MATCHED THEN
19 INSERT (b.employee_id,b.bonus_amt)
20 VALUES (e.employee_id,e.salary*0.1)
21 WHERE e.salary<7500

 

posted @ 2015-07-21 18:23  csnmd  阅读(258)  评论(0)  编辑  收藏  举报