oracle关联更新

update某个表时，如果需要和其他表关联，这样的SQL语句在ORACLE中实现起来比较呕心。各个where条件需要分别与一堆select语句嵌套使用才可，晕~~
以下sql语句是在工作中写出的，做个备忘录：

--  更新应收账款净额
update  t_0303009 t1
    set  t1.item_value  =  
(
select  t2.item_value
   from
       (
             SELECT    a.product_id, a.item_id, b.item_value
                 FROM  ( SELECT  product_id, item_id, item_value
                         FROM  t_0303009
                        WHERE  item_id  LIKE   ' B%011 ' ) a,
                     ( SELECT  product_id, item_id, item_value
                         FROM  t_0303009
                        WHERE  item_id  LIKE   ' B%008 ' ) b
                WHERE  a.product_id  =  b.product_id
                  AND  SUBSTR (a.item_id,  2 ,  6 )  =  SUBSTR (b.item_id,  2 ,  6 )
                  AND  a.item_value  =   0
     ) t2
where  t1.product_id  =  t2.product_id
   and  t1.item_id  =  t2.item_id
)
where  t1.product_id  =  (  select  product_id  from  
       (
             SELECT    a.product_id, a.item_id, b.item_value
                 FROM  ( SELECT  product_id, item_id, item_value
                         FROM  t_0303009
                        WHERE  item_id  LIKE   ' B%011 ' ) a,
                     ( SELECT  product_id, item_id, item_value
                         FROM  t_0303009
                        WHERE  item_id  LIKE   ' B%008 ' ) b
                WHERE  a.product_id  =  b.product_id
                  AND  SUBSTR (a.item_id,  2 ,  6 )  =  SUBSTR (b.item_id,  2 ,  6 )
                  AND  a.item_value  =   0
     ) jj
      where  t1.product_id  =  jj.product_id
        and  t1.item_id  =  jj.item_id
     )
and  t1.item_id  =  (  select  item_id  from  
       (
             SELECT    a.product_id, a.item_id, b.item_value
                 FROM  ( SELECT  product_id, item_id, item_value
                         FROM  t_0303009
                        WHERE  item_id  LIKE   ' B%011 ' ) a,
                     ( SELECT  product_id, item_id, item_value
                         FROM  t_0303009
                        WHERE  item_id  LIKE   ' B%008 ' ) b
                WHERE  a.product_id  =  b.product_id
                  AND  SUBSTR (a.item_id,  2 ,  6 )  =  SUBSTR (b.item_id,  2 ,  6 )
                  AND  a.item_value  =   0
                     ) jj2
                      where  t1.product_id  =  jj2.product_id
                        and  t1.item_id  =  jj2.item_id
 )

 

 

转载于:https://www.cnblogs.com/heekui/archive/2007/12/15/995633.html