Oracle如何使用“合并到”进行基于选择的插入或更新

xiozqbni 于 2023-04-11 发布在 Oracle

关注(0)|答案(2)|浏览(146)

我有一个客户表，如图所示
基于选择结果，我正尝试基于它执行插入或更新操作

CREATE TABLE customers  
(
    customer_id     number(10) NOT NULL,  
    customer_serial number(10)
);

我尝试做以下3个步骤
1.我正在根据customer_id从customers获取customer_serial
1.如果未找到数据，则插入到客户中（25是customer_serial的硬编码值）
1.否则，如果找到数据，则进行更新（向现有customer_serial添加10）
验证码：

MERGE INTO customers 
USING (SELECT customer_serial 
       FROM customers 
       WHERE customer_id = 2) cust 

WHEN NOT MATCHED 
    THEN 
        INSERT (customer_id, customer_serial) 
        VALUES (customer_id, 25) 

WHEN MATCHED 
    THEN 
        UPDATE 
            SET customer_serial = cust.customer_serial + 10 
        WHERE 
            customer_id = 2;

https://dbfiddle.uk/CpyHhLUY

oracle

来源：https://stackoverflow.com/questions/75941184/oracle-how-to-use-merge-into-for-insert-or-update-based-on-select

2条答案

按热度按时间

nbysray51#

我想你需要以下语法：

merge into customers c
using (select 2 as customer_id from dual) s
on (c.customer_id = s.customer_id)
when not matched then insert (customer_id, customer_serial) values (s.customer_id , 25) 
when matched then update set c.customer_serial = c.customer_serial + 10

从using子句中定义的给定客户id（这里是2）开始，查询在表中搜索相应的记录（即on子句）。如果不存在这样的行，则when not matched子句插入具有固定序列25的id，否则when matched子句递增现有序列。
fiddle

赞(0）回复(0）举报 2023-04-11

6jygbczu2#

您缺少ON子句，可以将查询简化为：

MERGE INTO customers
USING DUAL
ON (customer_id = 2)
WHEN NOT MATCHED THEN 
  INSERT (customer_id, customer_serial) VALUES (2, 25) 
WHEN MATCHED THEN 
  UPDATE SET customer_serial = customer_serial + 10;

fiddle

赞(0）回复(0）举报 2023-04-11

我来回答

Oracle如何使用“合并到”进行基于选择的插入或更新

2条答案

相关问题

热门标签

最新问答