Skip to Main Content
  • Questions

  • sql query to update a table based on data from other table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Subhash.

Asked: September 24, 2017 - 4:02 pm UTC

Last updated: September 28, 2017 - 4:01 pm UTC

Version: 12.1

Viewed 1000+ times

You Asked

Hi,

Looks like my other similar questions got closed, so asking a new question.

I have a cust_bug_data table with 2 columns(ROOT_CAUSE, BUG_NUMBER) like as follows: 
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);


I have following query 
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



and there is another table
create table cust_bug_tag(BUG_NUMBER NUMBER REFERENCES cust_bug_data(BUG_NUMBER),tag VARCHAR(250), primary key (bug_nubmer, tag));


Now if I use following query it will insert 5 rows into cust_bug_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


Now if I insert one more row(4th row) in my first table cust_bug_data
insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('#new issue, #test', 261672);


Then could you please let me know what would be the query which will only insert that 4th rows content into second table cust_bug_tag and cust_bug_tag should look like 

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


Actually I want to run following query in an interval(daily)
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


and this should insert only insert new(4th row here for example) rows entries(bug_number, tag) into cust_bug_tag.

cust_bug_data table will be updated daily and based on that only the delta rows should be inserted into cust_bug_tag.

There is one more issue. If the root_cause column in cust_bug_data doesn't have a #taged keyword then query should not insert any row in cust_bug_tag.

like insert into cust_bug_data(ROOT_CAUSE, BUG_NUMBER ) values('This is a not an issue', 257241);
for above there should not be any (bug_number,tag) return row as there is not # in root_cause column and for this row there should not be any entry in cust_bug_tag

Thanks,
Subhash.

and Chris said...

If you want to add bug_numbers that aren't already in the table, you just need a not exists clause:

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


Of course, this means you're querying both tables instead of just inserting. Which increases the work you're doing. You're better off inserting the values into the tag table at the same time as you add them to data.

If you want to get fancy, you can do this in one statement with insert all!

To do this remove the regular expression from the where clause. Then only insert into tag when this 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 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


Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.