Oracle 表关联性 Update 语句的改写,推荐改写方法1

同事写了一个逻辑稍复杂的Update 语句，觉得在代码可读性上有些转圈，交给我帮忙改下。

以下根据原SQL，使用两种方法进行改写，个人推荐方法1的改写。方法2拆分两个SQL来写，代码可读性最强，但是SQL扩展上不利，推荐方法1的改写.

原SQL：

UPDATE z_delivery_orders a
     SET dtheday2 = a.dtheday2  +
               (SELECT c.add_dt
                FROM ud_cfg_firmdt c
                  WHERE c.add_inv_type = (CASE WHEN

　　　　　　　　　　　　a.VBELN IN (SELECT VBELN FROM Z_MID_PCDW_SO b WHERE b.zcustseg = 'WEB')

　　　　　　　　　　　　THEN 'A'
　　　　　　　　　　　　ELSE
                                           'B'
                                           END)) )
     WHERE a.dtheday2 IS NOT NULL;

update SQl 本身是没有问题的，逻辑也是符合要求，且不说表的设计上是否合理，单从SQL的改写上着手做增强。

改写1：

MERGE INTO z_delivery_orders a

USING (select rowid as row_id,

               (CASE

                 WHEN VBELN IN (SELECT VBELN

                                  FROM Z_MID_PCDW_SO b

                                 WHERE b.zcustseg = 'WEB') THEN

                  'A'

                 ELSE

                  'B'

               END) as type_1

          from z_delivery_orders

         WHERE dtheday2 IS NOT NULL ) b

ON (a.rowid = b.row_id)

WHEN MATCHED THEN

   UPDATE

      SET a.dtheday2 =

          (SELECT  a.dtheday2  + nvl(max(add_dt), 0)  as dtheday2

             FROM ud_cfg_firmdt

            WHERE add_inv_type = b.type_1);

 

改写2：拆分两个SQL来写，代码可读性最强，但是SQL扩展上不利，推荐方法1的改写

UPDATE z_delivery_orders a

   SET dtheday2 =  dtheday2  +

                          (SELECT add_dt

                             FROM ud_cfg_firmdt

                            WHERE add_inv_type = ’A'

                              and rownum = 1) 

WHERE dtheday2 IS NOT NULL

   AND exists (select 1

          from Z_MID_PCDW_SO b

         WHERE b.zcustseg = 'WEB'

           and a.vbeln = b.vbeln);

           

  UPDATE z_delivery_orders a

     SET dtheday2 = dtheday2, +

                            (SELECT add_dt

                               FROM ud_cfg_firmdt

                              WHERE add_inv_type = 'B'

                                and rownum = 1) 

   WHERE dtheday2 IS NOT NULL 

     AND not exists (select 1

            from Z_MID_PCDW_SO b

           WHERE b.zcustseg = 'WEB'

             and a.vbeln = b.vbeln)