方法一:MERGE语句的语法
MERGE INTO 表名 USING 表名/视图/子查询 ON 连接条件 --多个条件注意()括起来 WHEN MATCHED THEN -- 当匹配得上连接条件时 更新、删除操作 WHEN NOT MATCHED THEN -- 当匹配不上连接条件时 更新、删除、插入操作
示例
MERGE INTO CAI_GRKMX a USING TMP_IMPHD b ON (a.SPID=b.SPID AND a.PIHAO=b.PIHAO AND instr(b.pihao,a.djbh)>0) WHEN MATCHED THEN UPDATE SET a.HDUID=b.MXUID,a.HDBZ=1; COMMIT;
来自网上更好的说明
MERGE INTO dept60_bonuses b USING ( SELECT employee_id, salary, department_id FROM hr.employees WHERE department_id = 60 ) e ON (b.employee_id = e.employee_id) -- 当符合关联条件时 WHEN MATCHED THEN -- 将奖金为0的员工的奖金调整为其工资的20% UPDATE SET b.bonus_amt = e.salary * 0.2 WHERE b.bonus_amt = 0 -- 删除工资大于7500的员工奖金记录 DELETE WHERE (e.salary > 7500) -- 当不符合连接条件时 WHEN NOT MATCHED THEN -- 将不在部门为60号的,且不在dept60_bonuses表的用工信息插入,并将其奖金设置为其工资的10% INSERT (b.employee_id, b.bonus_amt) VALUES (e.employee_id, e.salary * 0.1) WHERE (e.salary < 7500)
方法二:作为多表级联更新的另外一种写法
UPDATE (SELECT a.HDUID,b.MXUID,HDBZ FROM CAI_GRKMX a INNER JOIN TMP_IMPHD b ON a.SPID=b.SPID AND a.PIHAO=b.PIHAO AND instr(b.pihao,a.djbh)>0 ) SET HDUID=MXUID,HDBZ=1