Oracle 使用查找表更新表的列

嗨，
我有一张表

create table temp1(product_id number, area varchar2(50), owner varchar2(50));
复制

以下是dml上的操作:

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');
复制

还有一张表

create table temp2(product_id number, subject varchar2(200),bug_number number, ASSIGNEE varchar2(50));
复制

以下是dml上的操作:

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);
复制

从temp2中选择 *
退货:

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
复制

现在，我必须根据表temp1的product_id，区域和所有者更新表temp2的受让人列。temp2的主题列将具有temp1的区域列值作为子字符串。像temp2列主题 “这是管理服务器错误” 一样，具有子字符串 “管理服务器”，并且该子字符串存在于表temp1的区域列中。


我需要有如下的输出:
从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
复制

请让我知道在表temp2上执行上述更新的sql

谢谢，
子哈希

因此，当产品ID匹配且区域位于temp2的主题中时，您想从temp1.owner中设置temp2.assignee？

如果是这样，你需要一个相关的更新。在t1中选择匹配的行。您可以使用instr查看主题是否包含区域:

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
复制