Oracle 之 merge 误区讨论
merge 五大误区
- 不能更新 ON 子句引用的列
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.NAME=T1.NAME;
ORA-38104: 无法更新 ON 子句中引用的列: “T2”.“NAME”
- DELETE 子句的 WHERE 顺序必须最后
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY
DELETE WHERE (T2.NAME = ‘A’)
WHERE T1.NAME=‘A’;
ORA-00933: SQL 命令未正确结束
–改为如下即可：
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY
WHERE T1.NAME=‘A’
DELETE WHERE (T2.NAME = ‘A’);
3.DELETE 子句只可以删除目标表，而无法删除源表, 无论是否在 where 后面加原表限制条件，都无法删除原表的数据
/*
这里需要引起注意，无论 DELETE WHERE (T2.NAME = ‘A’) 这个写法的 T2 是否改写为 T1，效果都一样，都是对目标表进行删除！
*/
4. 更新同一张表的数据, 需担心 USING 的空值
/*
需求为对 T2 表进行自我更新，如果在 T2 表中发现 NAME=D 的记录，就将该记录的 MONEY 字段更新为 100，如果 NAME=D 的记录不存在，
则自动增加，NAME=D 并且 MONEY=100 的记录。根据语法完成如下代码:
*/
MERGE INTO T2
USING (select * from t2 where NAME=‘D’) T
ON (T.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=100
WHEN NOT MATCHED THEN
INSERT
VALUES (‘D’,200);
–但是查询发现，本来 T 表应该因为 NAME=D 不存在而要增加记录，但是实际却根本无变化。
/*
原来是因为此时 select * from t2 where NAME=’D’为 NULL, 所以出现了无法插入的情况，
我们可以利用 COUNT(*) 的值不会为空的特点来等价改造，具体如下:
*/
MERGE INTO T2
USING (select COUNT(*) CNT from t2 where NAME=‘D’) T
ON (T.CNT<>0)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=100
WHEN NOT MATCHED THEN
INSERT
VALUES (‘D’,100);
- 必须要在源表中获得一组稳定的行
—构造数据，请注意这里多插入一条 A 记录，就产生了 ORA-30926 错误
INSERT INTO T1 VALUES (‘A’,30);
COMMIT;
—此时继续执行如下
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY;
ORA-30926: 无法在源表中获得一组稳定的行
/*
oracle 中的 merge 语句应该保证 on 中的条件的唯一性，T1.NAME=T2.NAME 的时候，T1 表记录对应到了 T2 表的两条记录，所以就出错了。
解决方法很简单，比如我们可以对 T1 表和 T2 表的关联字段建主还键，这样基本上就不可能出现这样的问题，而且一般而言，MERGE 语句的关联字段互相有主键，
MERGE 的效率将比较高！或者是将 T1 表的 ID 列做一个聚合，这样归并成单条，也能避免此类错误。如：
*/
MERGE INTO T2
USING (select NAME,SUM(MONEY) AS MONEY FROM T1 GROUP BY NAME)T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY;
–正常情况下，一般出现重复的 NAME 需要引起怀疑，不太应该。