Oracle SQL合并以插入和删除但不更新 [英] Oracle sql merge to insert and delete but not update

问题描述

是否有一种使用oracle merge插入和删除但不更新的方法?

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

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

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.

这是一个带有更新的有效示例.为了使此工作有效，我必须添加dummy以便可以更新不在on条件下的列.有什么方法可以只删除和插入而无需虚拟列来更新吗?

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?

update set列表中的任何列都不能出现在update set列表中，即使它实际上没有被更新.

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

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和oracle 11g中使用 sqlfiddle 对此进行了尝试.

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

推荐答案

否，您不能删除未通过merge命令更新的行.
这是文档: http://docs.oracle.com/cd/B28359_01/server. 111/b28286/statements_9016.htm

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

指定DELETE where_clause来清理表中的数据，同时 填充或更新它. 此子句影响的唯一行是 目标表中由合并更新的那些行 操作. DELETE WHERE条件评估更新后的值，而不是 UPDATE SET ... WHERE评估的原始值 健康)状况.如果目标表的行符合DELETE 条件，但不包含在ON子句定义的连接中， 那么它不会被删除.在目标上定义的任何删除触发器 删除每行都会激活该表.

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子句

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

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

这篇关于Oracle SQL合并以插入和删除但不更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！