使用多个表中的数据更新列

Question

我希望从表IAM_RSP_INSTRUCTION更新列TOTAL_DURATION中的值，其中TOTAL_DURATION的值基于查询

select (paid_rsp + rd) as total from 
(
  select count (b.rsp_instruction_id) as paid_rsp, a.rsp_duration as rd,
    b.rsp_instruction_id
  from IAM_RSP_INSTRUCTION a
  JOIN IAM_BUY_FUND_INFO b on a.RSP_ID = b.RSP_INSTRUCTION_ID
  where b.RSP_INSTRUCTION_ID is not null
  and a.rsp_status= 'approved' or a.rsp_status='terminated' 
  group by a.rsp_duration, b.rsp_instruction_id, a.rsp_status<br>
  HAVING a.rsp_duration > -1
  order by b.rsp_instruction_id
) ,
and rsp_id from IAM_RSP_INSTRUCTION = rsp_instruction_id
from IAM_BUY_FUND_INFO

目前，我有一个更新查询：

UPDATE IAM_RSP_INSTRUCTION
SET j.TOTAL_DURATION = (
  select (paid_rsp + rd)  as total
  from (
    select count (b.rsp_instruction_id) as paid_rsp, a.rsp_duration as rd,
      b.rsp_instruction_id 
    from iam_rsp_instruction a
    JOIN iam_buy_fund_info b on a.RSP_ID = b.RSP_INSTRUCTION_ID
    where b.RSP_INSTRUCTION_ID is not null
    and a.rsp_status= 'approved' or a.rsp_status='terminated'
    group by a.rsp_duration, b.rsp_instruction_id, a.rsp_status
    HAVING a.rsp_duration > -1
    order by b.rsp_instruction_id
  )
  WHERE IAM_RSP_INSTRUCTION.rsp_id = rsp_instruction_id
);

当我运行查询时，8小时后，它仍然在运行，并且没有更新任何记录。

注意:当我运行该子查询时，它可以工作。

select (paid_rsp + rd) as total from 
    (
      select count (b.rsp_instruction_id) as paid_rsp, a.rsp_duration as rd,
        b.rsp_instruction_id
      from IAM_RSP_INSTRUCTION a
      JOIN IAM_BUY_FUND_INFO b on a.RSP_ID = b.RSP_INSTRUCTION_ID
      where b.RSP_INSTRUCTION_ID is not null
      and a.rsp_status= 'approved' or a.rsp_status='terminated' 
      group by a.rsp_duration, b.rsp_instruction_id, a.rsp_status<br>
      HAVING a.rsp_duration > -1
      order by b.rsp_instruction_id
    ) 

请帮帮忙。谢谢。

Answer 1

以下是我所能找到的最明显问题的修复。如果你提供表结构和一些示例，这是可以测试的。

UPDATE IAM_RSP_INSTRUCTION j 
   SET j.TOTAL_DURATION = (select (paid_rsp + rd)  as total 
                             from (select count (b.rsp_instruction_id) as paid_rsp, 
                                          a.rsp_duration as rd,
                                          b.rsp_instruction_id 
                                     from iam_rsp_instruction a 
                                          JOIN 
                                          iam_buy_fund_info b 
                                          on a.RSP_ID = b.RSP_INSTRUCTION_ID
                                    where b.RSP_INSTRUCTION_ID is not null 
                                      and (a.rsp_status= 'approved' 
                                       or a.rsp_status='terminated')
                                      and b.rsp_instruction_id = j.rsp_id
                                 group by a.rsp_duration,
                                          b.rsp_instruction_id, 
                                          a.rsp_status 
                                   HAVING a.rsp_duration > -1));