MERGE

MERGE语句具有按条件获取要更新或插入到表中的数据行，然后从一个或多个数据源投对表进行更新或者向表中插入行两方面的能力。它最经常被用在数据仓库中来移动大量的数据。

MERGE <hint>
INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

MERGE INTO dept60_bonuses b USING (
    SELECT
        employee_id,
        salary,
        department_id
    FROM
        employees
    WHERE
        department_id = 60
) e ON (b.employee_id = e.employee_id)
WHEN matched THEN UPDATE
SET b.bonus_amt = e.salary * 0.2
WHERE
    b.bonus_amount = 0 
DELETE WHERE
    (e.salary > 7500)
WHEN NOT matched THEN
    INSERT (b.employee_id, b.bonus_amt)
VALUES
    (e.employee_id, e.salary * 0.1)
WHERE
    (e.salary < 7500);

其实一般用的写法都没这么复杂，一般也就是：

MERGE INTO tbl1 a
USING tbl2 b
ON a.id = b.id
WHEN MATCHED THEN UPDATE
SET a.col1=b.col, a.col2=b.col2
WHEN NOT MATCHED THEN INSERT
(a.col1, a.col2) VALUES (b.col1, b.col2);