Oracle 基于其他表的数据更新表的sql查询

嗨，

看来我其他类似的问题已经解决了，所以问了一个新问题。

我有一个cust_bug_data表，有2列 (ROOT_CAUSE，BUG_NUMBER)，如下所示:

create table cust_bug_data(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER NOT NULL PRIMARY KEY);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('This is a #test issue and not a #code issue.', 25940149);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('This is a #timing issue related to #database', 25768241);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('#timing issue', 26167402);
复制

我有以下查询

SELECT distinct   bug_number, replace(regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
FROM cust_bug_data where regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') is not null
CONNECT BY regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL 

which gives output like as follows:
BUG_NUMBER        TAG                        
-------------   ------
25940149         test 
25940149         code 
25768241         timing 
25768241         database
26167402         timing
复制

还有另一张表

create table cust_bug_tag(BUG_NUMBER NUMBER REFERENCES cust_bug_data(BUG_NUMBER),tag VARCHAR(250), primary key (bug_nubmer, tag));
复制

现在，如果我使用以下查询，它将在cust_bug_tag中插入5行

insert into cust_bug_tag(bug_number, tag)
SELECT distinct   bug_number, replace(regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
FROM cust_bug_data where regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') is not null
CONNECT BY regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL 
复制

现在，如果我在第一个表cust_bug_data中再插入一行 (第4行)

insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('#new issue, #test', 261672);
复制

然后，您能否让我知道将仅将第4行内容插入第二个表cust_bug_tag和cust_bug_tag的查询应该像

BUG_NUMBER        TAG                        
-------------   ------
25940149         test 
25940149         code 
25768241         timing 
25768241         database
26167402         timing
261672           new 
261672           test
复制

实际上，我想在间隔 (每天) 内运行以下查询

insert into cust_bug_tag(bug_number, tag)
SELECT distinct   bug_number, replace(regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
FROM cust_bug_data where regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') is not null
CONNECT BY regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL 
复制

并且这应该仅将插入新的 (例如此处的第4行) 行条目 (bug_number，tag) 插入cust_bug_tag。

cust_bug_data表将每天更新，并且基于此，仅增量行应插入cust_bug_tag中。

还有一个问题。如果cust_bug_data中的root_cause列没有 # taged关键字，则query不应在cust_bug_tag中插入任何行。

像插入cust_bug_data(ROOT_CAUSE，BUG_NUMBER) 值 ('这不是问题'，257241);
对于上面，不应存在任何 (bug_number，标记) 返回行，因为root_cause列中没有 #，并且对于该行，cust_bug_tag中不应存在任何条目

谢谢，
Subhash。

如果要添加表中还没有的bug_numbers，则只需要一个not exists子句:

create table cust_bug_data(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER NOT NULL PRIMARY KEY);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('This is a #test issue and not a #code issue.', 25940149);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('This is a #timing issue related to #database', 25768241);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('#timing issue', 26167402);

create table cust_bug_tag(BUG_NUMBER NUMBER REFERENCES cust_bug_data(BUG_NUMBER),tag VARCHAR(250), primary key (bug_number, tag));

insert into cust_bug_tag(bug_number, tag)
SELECT distinct   bug_number, replace(regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
FROM cust_bug_data where regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') is not null
CONNECT BY regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL ;

select * from cust_bug_tag;

BUG_NUMBER  TAG       
26167402    timing    
25940149    code      
25768241    database  
25940149    test      
25768241    timing  

insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('#new issue, #test', 261672);
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('This is a not an issue', 257241);

insert into cust_bug_tag(bug_number, tag)
SELECT distinct   bug_number, replace(regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
FROM cust_bug_data d
where  not exists (
  select null from cust_bug_tag t
  where  t.bug_number = d.bug_number
) 
and    regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') is not null
CONNECT BY regexp_substr(root_cause,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL ;

select * from cust_bug_tag
order  by 1 , 2;

BUG_NUMBER  TAG       
261672      new       
261672      test      
25768241    database  
25768241    timing    
25940149    code      
25940149    test      
26167402    timing  
复制

当然，这意味着您正在查询两个表，而不仅仅是插入。这增加了你正在做的工作。您最好在将值添加到数据的同时将值插入到标签表中。

如果你想花哨，你可以在一个语句中插入全部!

为此，请从where子句中删除正则表达式。然后只有插入到标签时，这不是null:

insert all 
  when rownum = 1 then into cust_bug_data (ROOT_CAUSE, BUG_NUMBER ) values (str, bug_number)
  when tag is not null then into cust_bug_tag (bug_number, tag) values (bug_number, tag)
with rws as (
  select 0 bug_number, '#test #stuff' str from dual
) 
  select bug_number, str, replace(regexp_substr(str,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
  from   rws
  CONNECT BY regexp_substr(str,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL ;
  
insert all 
  when rownum = 1 then into cust_bug_data (ROOT_CAUSE, BUG_NUMBER ) values (str, bug_number)
  when tag is not null then into cust_bug_tag (bug_number, tag) values (bug_number, tag)
with rws as (
  select 1 bug_number, 'test stuff' str from dual
) 
  select bug_number, str, replace(regexp_substr(str,'\#[a-z0-9_]+',1,level,'i'),'#','') tag
  from   rws
  CONNECT BY regexp_substr(str,'\#[a-z0-9_]+',1,level,'i') IS NOT NULL ;
  
select * from cust_bug_data;

ROOT_CAUSE                                    BUG_NUMBER  
This is a #test issue and not a #code issue.  25940149    
This is a #timing issue related to #database  25768241    
#timing issue                                 26167402    
#new issue, #test                             261672      
This is a not an issue                        257241      
#test #stuff                                  0           
test stuff                                    1

select * from cust_bug_tag
order  by 1, 2;

BUG_NUMBER  TAG       
0           stuff     
0           test      
261672      new       
261672      test      
25768241    database  
25768241    timing    
25940149    code      
25940149    test      
26167402    timing
复制