Oracle merge into 为何只能update不能insert ？

最新推荐文章于 2025-04-17 23:56:43 发布

Hehuyi_In

最新推荐文章于 2025-04-17 23:56:43 发布

阅读量4.3k

点赞数 1

分类专栏： Oracle

本文链接：https://blog.csdn.net/Hehuyi_In/article/details/106268300

版权

Oracle 专栏收录该内容

189 篇文章

订阅专栏

一、问题描述

开发反馈遇到一个奇怪的问题，使用merge语句时只能update但不能insert，简化后语句如下

create table tmp0521("id" int,"state" int);

merge into tmp0521 a
using (select "id","state" from tmp0521 where "id" = 1) b
on (a."id" = b."id")
when matched then
    update set
        a."state"=2
when not matched then
    insert ("id", "state")
values
    (1,0);

如果表中id='1'的值存在，update能执行成功；如果不存在，语句不报错，但也不会insert，显示 0 rows merged。

二、问题原因

merge into a using (...) b on (a.id=b.id)，真正含义其实是a表与b表比较，如果数据在：

a中存在，b中不存在 --> nothing
a中存在，b中也存在 --> update
a中不存在，b中存在 --> insert (b into a)
a中不存在，b中也不存在 --> nothing

看回前面那个语句，会发现b表其实是a表的子集，它不可能符合数据在“a中不存在，b中存在”这种情况，如果b中没有"id"=1的数据，a中一定也没有，所以它不可能执行insert操作。

a中不存在id=1的数据，b中也不存在，不执行任何操作

三、解决方法

法1. 改为分别使用update和insert

这个没啥好解释了，一定是可以的，并且逻辑也简单。

法2. 改写sql语句

将using中的 select "id","state" from tmp0521 where "id" = '1' 改为 select 1 "id",0 "state" from dual，此时a和b是不同的表，就可能出现数据在“a中不存在，b中存在”这种情况，可以执行insert。同时建议把 insert ("id", "state") values ('1',0) 对应改为 insert ("id", "state") values (t."id",t."state")。这个改写仅针对前面的sql案例，如果改写生产环境的sql，务必确认好逻辑，进行等价改写。

改写后语句如下：

merge into tmp0521 p
using (select 1 "id",0 "state" from dual) t  -- 改为dual
on (p."id" = t."id")
when matched then
    update set
        p."state"=2
when not matched then
    insert ("id", "state")
values
    (t."id",t."state"); -- 改为插入t表中数据

测试执行：