Oracle sql merge要插入和删除但不能更新

Is there a way to use oracle merge to insert and delete but not update?

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

I have a table representing a set of values related to a single row in another table. I could change the set of values by deleting them all and adding back the new set, or by selectively deleting some and adding others, but I am interested in making it a single statement if possible.

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

Here is a working example with update. In order to make this work, I had to add dummy so that a column was available to update that was not in the on condition. Is there some way to only delete and insert without a dummy column to update?

这是一个更新的工作示例。为了使这项工作，我不得不添加虚拟，以便可以更新一个不在开启状态的列。有没有办法只删除和插入没有虚拟列来更新？

No column from the on condition may be in the update set list even if it is not actually updated.

即使未实际更新，也不会在更新集列表中包含on条件中的列。

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;

I have tried this in oracle 10g and, with this sqlfiddle, oracle 11g.

我在oracle 10g中试过这个，用这个sqlfiddle，oracle 11g。

2 个解决方案

#1

No, you cannot delete rows that have not been updated by the merge command.
Here is documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

不，您无法删除merge命令尚未更新的行。这是文档：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.

指定DELETE where_clause以在填充或更新表时清理表中的数据。受此子句影响的唯一行是目标表中由合并操作更新的那些行。 DELETE WHERE条件评估更新的值，而不是UPDATE SET ... WHERE条件评估的原始值。如果目标表的一行符合DELETE条件但未包含在ON子句定义的连接中，则不会删除它。对于每行删除，将激活目标表上定义的任何删除触发器。

That means, that rows must be updated. Hovewer, you don't need to update all rows, after UPDATE use the same WHERE clause as you are using after DELETE

这意味着，必须更新行。 Hovewer，在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')

#2

I have found you can set the column to itself:

我发现你可以将列设置为自己：

MERGE ...
WHEN MATCHED THEN 
   UPDATE SET a_value = a_value WHERE a_value not in ('ace','afk')
   DELETE WHERE a_value not in ('ace','afk')

This negates the need for the dummy column.

这消除了对虚拟列的需要。

#1