Oracle如何使用Merge Into进行基于select的插入或更新

Question

我有一个客户表，如下所示

基于select结果，我正在尝试基于它进行插入或更新操作

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

我正在尝试做以下3个步骤

1. 我基于customer_id从客户获取customer_serial

2. 如果没有找到数据，向客户进行插入(25是customer_serial的硬编码值)

3. 如果发现数据，则执行更新(向现有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

Answer 1

我认为您需要以下语法:

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子句将增加现有的序列。

小提琴

Answer 2

您缺少一个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;

小提琴