merge 语句用法

最新推荐文章于 2025-05-10 20:02:24 发布

iteye_15396

最新推荐文章于 2025-05-10 20:02:24 发布

阅读量427

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle基础

本文链接：https://blog.youkuaiyun.com/iteye_15396/article/details/82437108

oracle基础专栏收录该内容

27 篇文章

订阅专栏

create table TEST_EMPLOYEES(ID NUMBER,NAME NVARCHAR2(50),SALARY NUMBER);   
insert into TEST_EMPLOYEES (ID, NAME, SALARY) values (1, '张三', 8000);   
insert into TEST_EMPLOYEES (ID, NAME, SALARY) values (2, '李四', 7000);   
insert into TEST_EMPLOYEES (ID, NAME, SALARY) values (3, '王五', 9000);   
create table TEST_EMPLOYEES2(ID NUMBER,NAME NVARCHAR2(50),SALARY NUMBER);   
insert into TEST_EMPLOYEES2 (ID, NAME, SALARY) values (1, '张三', 8000);   
insert into TEST_EMPLOYEES2 (ID, NAME, SALARY) values (2, '李四', 17000);   
insert into TEST_EMPLOYEES2 (ID, NAME, SALARY) values (3, '王五', 19000);

merge语句的语法如下：

merge <hint>
into <table_name>
using <table_view_or_query>
on (<condition>)
when matched then <update_clause>
delete <where_clause>
when not matched then <insert_clause>
[log errors <log_errors_clause> <reject limit < integer | unlimited > ]

具体用法如下：

merge into test_employees2 a
using (select id, name, salary, dept from test_employees) b
on (a.id = b.id)
when matched then
  update set a.name = b.name, a.salary = b.salary 
  delete where (a.id = 2)
when not matched then
  insert (id, name, salary, dept) values (b.id, b.name, b.salary, b.dept);