sql - Oracle：更新旧行时创建新行

Question

嗨，我正在尝试在下面创建一个新行，同时更新旧行值。

我有一个名为 TestCustomer 的下表，我想将现有行状态更新为“I”，如果插入时 customer_id 匹配，则使用新的 email_Id 和状态“A”创建一个新行；

create table TestCustomer(customer_id varchar(18),emai_id varchar(18),email_stat char(1))

Insert Into TestCustomer(customer_id,emai_id,email_stat)values('223459','12345','A');
Insert Into TestCustomer(customer_id,emai_id,email_stat)values('223458','123456','I');
Insert Into TestCustomer(customer_id,emai_id,email_stat)values('223459','123457','A');

输入：

customer_id=223459 emai_id='23678'

输出：

输出

标签： sqloracleoracle11goracle10g

Answer 1

您可以使用merge在子句中包含 2 行的语句来获取它using：一个 for update，第二个 for insert：

merge into TestCustomer t
using (
      select
         c,
         223459  as customer_id,
         '23678' as emai_id
      from dual
           ,(select 'A' c from dual union all select 'X' c from dual)
) v
on (v.c='X' and t.customer_id=v.customer_id)
when matched then update
    set email_stat='I'
when not matched then
    insert (customer_id,emai_id,email_stat)
    values(v.customer_id,v.emai_id,v.c)
    where v.c='A';

如您所见，带有“X”的行用于更新，带有“A”的行用于更新。

完整示例：https ://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=4f014283e5eff2e4cc81d09f0bbfca9b