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

前端之家收集整理的这篇文章主要介绍了Oracle sql合并插入和删除但不更新前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
有没有办法使用oracle合并来插入和删除但不能更新?

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

这是一个更新的工作示例.为了使这项工作,我不得不添加虚拟,以便一列可用于更新,不是在条件.有没有办法只删除和插入没有一个虚拟列来更新?

即使没有实际更新,也不能在更新设置列表中显示条件的列.

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,'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.

否,您不能删除由merge命令未更新的行. @H_502_15@这里是文档: 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@H_502_15@ populating or updating it. The only rows affected by this clause are@H_502_15@ those rows in the destination table that are updated by the merge@H_502_15@ operation. The DELETE WHERE condition evaluates the updated value,not@H_502_15@ the original value that was evaluated by the UPDATE SET … WHERE@H_502_15@ condition. If a row of the destination table meets the DELETE@H_502_15@ condition but is not included in the join defined by the ON clause,@H_502_15@ then it is not deleted. Any delete triggers defined on the target@H_502_15@ table will be activated for each row deletion.

这意味着必须更新行. 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')

猜你在找的Oracle相关文章