merge用法详解

1、普通用法：

MERGE INTO EMPLOYE AS EMUSING (SELECT * FROM MANAGER WHERE MA.SALARY > 2) AS MAON (EM.EMPLOYEID = MA.MANAGERID)WHEN MATCHED AND EM.SALARY < MA.SALARY THEN UPDATE SET EM.SALARY = MA.SALARYWHEN NOT MATCHED THEN  INSERT VALUES (MA.MANAGERID, MA.NAME, MA.SALARY);

2、添加其它条件：

MERGE INTO EMPLOYE AS EMUSING (SELECT * FROM MANAGER WHERE MA.SALARY > 2) AS MAON (EM.EMPLOYEID = MA.MANAGERID)WHEN MATCHED AND EM.SALARY < MA.SALARY THEN UPDATE SET EM.SALARY = MA.SALARYWHEN NOT MATCHED THEN  INSERT VALUES (MA.MANAGERID, MA.NAME, MA.SALARY);

3、忽略某个条件

MERGE INTO EMPLOYE AS EMUSING MANAGER AS MAON EM.EMPLOYEID = MA.MANAGERID WHEN MATCHED AND EM.SALARY < MA.SALARY THEN  UPDATE SET EM.SALARY = MA.SALARYWHEN  NOT MATCHED THEN  INSERT VALUES (MA.MANAGERID, MA.NAME, MA.SALARY)  ELSE IGNORE 

4、抛异常:

MERGE INTO EMPLOYE AS EMUSING MANAGER AS MAON EM.EMPLOYEID = MA.MANAGERIDWHEN MATCHED THEN AND EM.SALARY < MA.SALARY THEN UPDATE SET EM.SALARY = MA.SALARYWHEN MATCHED AND EM.SALARY > MA.SALARY THEN SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'EM.SALARY > MA.SALARY'WHEN NOT MATCHED THEN  INSERT VALUES (MA.MANAGERID, MA.NAME, MA.SALARY);

5、删除:

MERGE INTO EMPLOYE AS EM USING MANAGER MA ON EM.EMPLOYEID = MA.MANAGERID WHEN MATCHED AND EM.SALARY < MA.SALARY THEN   UPDATE SET EM.SALARY = MA.SALARYWHEN MATCHED AND EM.SALARY > MA.SALARY THEN    DELETEWHEN NOT MATCHED THEN    INSERT VALUES (MA.MANAGERID, MA.NAME, MA.SALARY);