Hi,
I have a table
create table temp1(product_id number, area varchar2(50), owner varchar2(50));
and following is the dml operations on it:
insert into temp1(product_id ,area,owner) values(5757,'cordinated replicat', 'apushar');
insert into temp1(product_id ,area,owner) values(5757,'admin server', 'subhasku');
insert into temp1(product_id ,area,owner) values(5,'admin server', 'abc');
There is another table
create table temp2(product_id number, subject varchar2(200),bug_number number, ASSIGNEE varchar2(50));
and following is the dml operations on it:
insert into temp2(product_id ,subject ,bug_number) values(5757,'this is an Admin Server bug',1234);
insert into temp2(product_id ,subject ,bug_number) values(5757,'this is a Cordinated Replicat bug',2345);
insert into temp2(product_id ,subject ,bug_number) values(5,'this is an Admin Server bug',54321);
select * from temp2
returns:
PRODUCT_ID SUBJECT BUG_NUMBER ASSIGNEE
----------- ------- ---------- --------
5757 this is a Cordinated Replicat bug 1234
5757 this is an Admin Server bug 5678
5 this is an Admin Server bug 54321
Now I have to update the assignee column of table temp2 based on the product_id,area and owner of table temp1. Subject column of temp2 will have area column value of temp1 as substring. like temp2 column subject "this is an Admin Server bug" has substring "Admin Server" and this substring is present in area column of table temp1.
I need to have output like as follows:
select * from temp2;
PRODUCT_ID SUBJECT BUG_NUMBER ASSIGNEE
----------- ------- ---------- --------
5757 this is a Cordinated Replicat bug 1234 apushar
5757 this is an Admin Server bug 5678 subhasku
5 this is an Admin Server bug 54321 abc
Please let me know the sql for performing above update on table temp2
Thanks,
Subhash
So you want to set temp2.assignee from temp1.owner when there's a match on product ID and the area is in the subject of temp2?
If so, you need a correlated update. Select the matching row in t1. You can use instr to see if the subject contains the area:
update temp2 t2
set assignee = (
select t1.owner from temp1 t1
where t1.product_id = t2.product_id
and instr(lower(t2.subject), t1.area) > 0
);
3 rows updated.
select * from temp2;
PRODUCT_ID SUBJECT BUG_NUMBER ASSIGNEE
5757 this is an Admin Server bug 1234 subhasku
5757 this is a Cordinated Replicat bug 2345 apushar
5 this is an Admin Server bug 54321 abc