两次连接更新 [英] Update with double joins

问题描述

我有一个访问查询，我需要将其转换为Oracle.在访问中，它使用内部联接中的内部联接.我不是这方面的专家，但我只学习了Oracle一定程度的知识，而实际上在访问方面一无所获. (也不是我的访问字符串(它的创建者一年前就去世了)).而且我没有选择将其转换为oracle ...它只需要完成即可. :/

I have an access query that I need to have converted to Oracle. And in access, it uses an inner join that is within an inner join. I am not an expert at any of this, but I only learned Oracle to an extent and practically nothing in access. (and is not my access string either (creator of it died a year back)). And I have no choice in its conversion to oracle... it just needs to get done. :/

无论如何...这是字符串:

anyhow... here is the string:

UPDATE AIRMODEL_NETWORK_SUMMARY
  INNER JOIN (HISTORY_BOL
    INNER JOIN PERIOD_TO_PROCESS
    ON
      (HISTORY_BOL.FSCL_WK_IN_YR_NUM = PERIOD_TO_PROCESS.FSCL_WK_IN_YR_NUM)
    AND
      (HISTORY_BOL.FSCL_YR_NUM = PERIOD_TO_PROCESS.FSCL_YR_NUM)
      )
  ON
    (AIRMODEL_NETWORK_SUMMARY.LOC_ID = HISTORY_BOL.TO_LOC_ID)
  AND
    (AIRMODEL_NETWORK_SUMMARY.FSCL_WK_IN_YR_NUM = HISTORY_BOL.FSCL_WK_IN_YR_NUM)
  AND
    (AIRMODEL_NETWORK_SUMMARY.FSCL_YR_NUM = HISTORY_BOL.FSCL_YR_NUM)
SET AIRMODEL_NETWORK_SUMMARY.CASES_RCVD = TOTAL_BOL_SHIP_CASE_QTY;

推荐答案

Oracle不同地喜欢它:

Oracle likes it differently:

MERGE
INTO    AIRMODEL_NETWORK_SUMMARY
USING   (
  SELECT TOTAL_BOL_SHIP_CASE_QTY, ROWID AS r 
  FROM AIRMODEL_NETWORK_SUMMARY
  INNER JOIN HISTORY_BOL
  ON
    (AIRMODEL_NETWORK_SUMMARY.LOC_ID = HISTORY_BOL.TO_LOC_ID)
  AND
    (AIRMODEL_NETWORK_SUMMARY.FSCL_WK_IN_YR_NUM = HISTORY_BOL.FSCL_WK_IN_YR_NUM)
  AND
    (AIRMODEL_NETWORK_SUMMARY.FSCL_YR_NUM = HISTORY_BOL.FSCL_YR_NUM)
  INNER JOIN PERIOD_TO_PROCESS
    ON
      (HISTORY_BOL.FSCL_WK_IN_YR_NUM = PERIOD_TO_PROCESS.FSCL_WK_IN_YR_NUM)
    AND
      (HISTORY_BOL.FSCL_YR_NUM = PERIOD_TO_PROCESS.FSCL_YR_NUM)
)
ON      (rowid = r)
WHEN MATCHED THEN
UPDATE
SET     CASES_RCVD = TOTAL_BOL_SHIP_CASE_QTY;

这篇关于两次连接更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！