使用查询结果更新表，而不删除和插入同一行

Question

我有一个表foo:

CREATE TABLE foo (
  bar   NUMBER NOT NULL,
  value VARCHAR2(10) NOT NULL
)

现在，在PL/SQL过程中，我想更改bar的特定值的值列表，使其恰好包含从相当复杂的选择查询返回的值：

SELECT * FROM (
  SELECT
    x bar,
    regexp_substr(y,'[^ ]+', 1, level) value
  FROM dual
  CONNECT BY regexp_substr(y, '[^ ]+', 1, level) IS NOT NULL
) WHERE TRIM(value) IS NOT NULL;

这意味着我必须（1）删除该查询未返回的所有行，以及（2）插入该查询中尚未在foo中的所有行。例如，如果foo包含这个，

1   A
2   A
2   B
2   C

变量x是2，并且上面的查询返回这个（因为y是C D）

2   C
2   D

我想最后得到一张这样的foo表：

1   A
2   C
2   D

目前，我首先删除bar = x所在的所有行，然后插入查询中的所有行：

DELETE FROM foo WHERE bar = x;
INSERT INTO foo
SELECT * FROM /* the full query edited out for compactness */;

问题是，这会打乱我的修订控制系统，因为我经常删除行，然后直接插入完全相同的行。是否有任何方法可以在不删除将再次插入的行的情况下执行？？我宁愿只键入一次查询，因为它相当长。

Answer 1

插入/删除foo和foo的新值之间的差异如何？

delete foo ff
 where exists(with c_query as ( 
               -- result of your complex query
               select 2 as bar, 'C' as value
                 from dual
               union all
               select 2 as bar, 'D' as value from dual)
              -- delete foo not in c_query
              (select f.bar, f.value
                 from foo f
                where f.bar = (select distinct bar from c_query)
                  and ff.bar = f.bar
                  and ff.value = f.value
               minus
               select d.bar, d.value from c_query d));
insert into foo
  with c_query as
   ( -- result of your complex query
    select 2 as bar, 'C' as value
      from dual
    union all
    select 2 as bar, 'D' as value from dual)
  -- insert c_query data not in foo    
   (select d.bar, d.value
      from c_query d
    minus
    select f.bar, f.value from foo f);