Hive的update转换

UPDATE T
FROM   VT_DIRECT_SHIPMENT_COST1 T
      ,(SELECT   Start_Setl_Org_Cd
                ,Send_Cust_Cd
                ,COUNT(1) AS Cnt
                ,SUM(Setl_Wt) AS Tot_Wt
        FROM    VT_DIRECT_SHIPMENT_COST1
       GROUP BY 1,2)S
SET    Share_Amt = ZEROIFNULL(T.Tot_Amt*T.Setl_Wt/NULLIFZERO(S.Tot_Wt))
WHERE  T.Start_Setl_Org_Cd = S.Start_Setl_Org_Cd
AND    T.Send_Cust_Cd = S.Send_Cust_Cd
;
.IF ERRORCODE <> 0 THEN .GOTO QUITWITHERROR

改写update后（均为临时表）

update的字段需要先判断关联字段是否为null

insert overwrite table temp.vt_direct_shipment_cost1
select 
     t.Shipment_No
    ,t.Trsf_Audit_Dt
    ,t.Start_Setl_Org_Cd
    ,t.Send_Cust_Cd
    ,t.Setl_Wt
    ,t.Tot_Amt
    ,t.Ship_Cnt
    ,case when S.Start_Setl_Org_Cd is not null and S.Send_Cust_Cd is not null then nvl(T.Tot_Amt*T.Setl_Wt/(case when S.Tot_Wt=0 then null else S.Tot_Wt end),0) end Share_Amt
from temp.vt_direct_shipment_cost1 T 
left join(
  S