oracle数据库插入或者更新判断是否存在记录的SQL

God__is__a__girl

已于 2024-01-25 08:43:08 修改

阅读量2.1k

点赞数 8

分类专栏：数据库文章标签： oracle

于 2024-01-24 16:50:23 首次发布

本文链接：https://blog.youkuaiyun.com/God__is__a__girl/article/details/135826150

版权

数据库专栏收录该内容

8 篇文章

订阅专栏

oracle数据库插入或者更新判断是否存在记录的SQL

1、插入的时候判断是否存在，若存在则忽略，若不存在则插入

写法①

insert into student(id,name,sex) 
select '111','张三','女' from dual
where not exists (select 1 from student where id= '111')

写法②

insert
when (not exists (select 1 from student where id= '111'))
  then into student(id,name,sex) values ('111','张三','女')
    select 1 from dual;

2、更新的时候判断是否存在，若存在则更新，若不存在则插入

写法①

begin
  update student set name = '张三' where id= '111'
if sql%notfound then
  insert into student(id,name,sex) values('111','张三','女')
end if;
end;

写法②

begin
  insert into student(id,name,sex)
  select '111','张三','女' from dual
  where not exists (select 1 from student where id= '111');
if sql%notfound then
  insert into student(id,name,sex) values('111','张三','女');
  update student set name = '张三' where id= '111';
end if;
end;
commit;