复杂的update语句

最新推荐文章于 2021-05-27 10:05:10 发布

原创最新推荐文章于 2021-05-27 10:05:10 发布 · 5.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#exe #date

oracle 专栏收录该内容

16 篇文章

订阅专栏

本文提供两个SQL更新脚本示例，分别用于更新代理的责任区域及管理区域，并同步从另一表中获取最新数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

UPDATE DIM_AGENT T

   SET T.DUTY_AREA =
       (SELECT L.DUTY_AREA
          FROM V_MU_AMS_AGENT L
         WHERE L.IATA_CODE = T.AGENT_IATA),
       T.ADMIN_AREA =
       (SELECT L.ADMIN_AREA
          FROM V_MU_AMS_AGENT L
         WHERE L.IATA_CODE = T.AGENT_IATA)
WHERE EXISTS (SELECT *
          FROM V_MU_AMS_AGENT L
         WHERE L.IATA_CODE = T.AGENT_IATA
           AND L.DUTY_AREA != T.DUTY_AREA)

    UPDATE
    (
      SELECT/*+ bypass_ujvc*/
             t.exe_company,
             k.company_cd
        FROM nw_flight_base t,
             (
               SELECT a.rowid AS rid, b.company_cd
                 FROM nw_flight_base a,
                      (SELECT t.*,
                              nvl(lead(apply_dt)
                                  over(PARTITION BY ac_no ORDER BY apply_dt) - 1,
                                  to_date('2999-12-31', 'yyyy-mm-dd')) next_apply_dt
                         FROM sa_aircraft t) b
                WHERE a.ac_no = b.ac_no
                  AND a.flight_dt BETWEEN b.apply_dt AND b.next_apply_dt
                  AND a.flight_dt BETWEEN p_flight_dt_from AND p_flight_dt_to
              ) k
      WHERE t.rowid = k.rid
    ) SET exe_company = company_cd;