sql - Oracle sql 合并插入和删除但不更新

Question

有没有办法使用oracle合并来插入和删除但不更新？

我有一个表，表示与另一个表中的一行相关的一组值。我可以通过删除所有值并重新添加新集来更改值集，或者通过有选择地删除一些并添加其他值来更改值集，但我有兴趣尽可能将其设为单个语句。

这是一个带有更新的工作示例。为了使这项工作，我不得不添加 dummy以便可以更新不在 on 中的列状况。有什么方法可以只删除和插入而没有要更新的虚拟列吗？

没有来自 on 的专栏条件可能在 update set列表，即使它实际上没有更新。

create table every_value ( the_value varchar2(32) );
create table paired_value ( the_id number, a_value varchar2(32) , dummy number default 0 );
-- the_id is a foreign_key to a row in another table

insert into every_value ( the_value ) values ( 'aaa' );
insert into every_value ( the_value ) values ( 'abc' );
insert into every_value ( the_value ) values ( 'ace' );
insert into every_value ( the_value ) values ( 'adg' );
insert into every_value ( the_value ) values ( 'aei' );
insert into every_value ( the_value ) values ( 'afk' );

-- pair ace and afk with id 3
merge into paired_value p using every_value e
on ( p.the_id = 3 and p.a_value = e.the_value )
when matched then update set dummy=dummy+1
delete where a_value not in ('ace','afk')
when not matched then insert (the_id,a_value)
values (3,e.the_value)
where e.the_value in ('ace','afk');

-- pair ace and aei with id 3
-- should remove afk, add aei, do nothing with ace
merge into paired_value p using every_value e
on ( p.the_id = 3 and p.a_value = e.the_value )
when matched then update set dummy = dummy+1
delete where a_value not in ('ace','aei')
when not matched then insert (the_id,a_value)
values (3,e.the_value)
where e.the_value in ('ace','aei');

-- pair aaa and adg with id 4
merge into paired_value p using every_value e
on ( p.the_id = 4 and p.a_value = e.the_value )
when matched then update set dummy = dummy+1
delete where a_value not in ('aaa','adg')
when not matched then insert (the_id,a_value)
values (4,e.the_value)
where e.the_value in ('aaa','adg');

select * from paired_value;

我已经在 oracle 10g 中尝试过这个，并且使用这个 sqlfiddle ，甲骨文11g。

Answer 1

不，您不能删除尚未被合并命令更新的行。
这是文档:http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.

这意味着，行 必须被更新。但是，您不需要更新所有行，在 UPDATE 之后使用与 DELETE 之后使用相同的 WHERE 子句

when matched then update set dummy=dummy
    where a_value not in ('ace','afk')
delete 
    where a_value not in ('ace','afk')