merge用法详解，

merge用法详解

1、普通用法：

MERGE INTO EMPLOYE AS EM
USING (SELECT * FROM MANAGER WHERE MA.SALARY > 2) AS MA
ON (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 EM
USING (SELECT * FROM MANAGER WHERE MA.SALARY > 2) AS MA
ON (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 EM
USING MANAGER AS MA
ON 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 EM
USING MANAGER AS MA
ON EM.EMPLOYEID = MA.MANAGERID
WHEN MATCHED THEN AND EM.SALARY < MA.SALARY THEN 
UPDATE SET EM.SALARY = MA.SALARY
WHEN 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.SALARY
WHEN MATCHED AND EM.SALARY > MA.SALARY THEN 
   DELETE
WHEN NOT MATCHED THEN 
   INSERT VALUES (MA.MANAGERID, MA.NAME, MA.SALARY);