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

问题描述

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

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?

on 条件中的任何列都可能不在 更新集 列表中，即使它实际上并未更新.

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 中尝试过这个，并且使用这个 sqlfiddle，oracle 11g.

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

推荐答案

不，您不能删除未被合并命令更新的行.
这是文档: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屋！