关于Oracle with as update 语句

1.利用with 更新单列是可以的，但如果要加入到限定条件 exists() 中就加不进去了，除非限定条件不用关联with表。也不可以同时更新多列。

update bu_price_history_client h set list_no=(
with w 
as(
SELECT * FROM(
 SELECT bd.id,b.list_no,b.cl_no,b.list_date,b.cp_no,bd.gs_no,bd.price_duty ,ROW_NUMBER() OVER(PARTITION BY cp_no,cl_no,gs_no ORDER BY b.list_date desc) AS rid
 from bu_buy b left join bu_buy_detail bd on b.list_no=bd.list_no 
 where b.ep_code='7F81D570009F3708E050007F01006E09' and bd.ep_code='7F81D570009F3708E050007F01006E09'
 )d WHERE rid =1
)
select list_no
from w where w.cp_no=h.cp_no and w.cl_no=h.cl_no and w.gs_no=h.gs_no
);

2.使用中尽量减少此类联合更新，太慢费资源。尽量做成联合查询插入语句，效率非常高，SQLserver2005及以上也可用，如：
insert into bu_price_history(gs_no,ph_type,cp_no
,list_no,list_date,price
,list_no_highest,list_date_highest,price_highest
,list_no_minimum,list_date_minimum,price_minimum,ep_code)
with 
zj as 
  (SELECT * FROM
  (SELECT bd.id,b.list_no,b.list_date,b.cp_no,bd.gs_no,bd.price_duty ,ROW_NUMBER() OVER(PARTITION BY cp_no,gs_no ORDER BY b.list_date desc) AS rid
  from bu_buy b left join bu_buy_detail bd on b.list_no=bd.list_no
  where b.ep_code='3E7214CA129E461DBB17242DBFA5544B' and bd.ep_code='3E7214CA129E461DBB17242DBFA5544B'
  )d WHERE rid =1),
zg as
  (SELECT * FROM
  (SELECT bd.id,b.list_no,b.list_date,b.cp_no,bd.gs_no,bd.price_duty ,ROW_NUMBER() OVER(PARTITION BY cp_no,gs_no ORDER BY bd.price_duty desc) AS rid
  from bu_buy b left join bu_buy_detail bd on b.list_no=bd.list_no
  where b.ep_code='3E7214CA129E461DBB17242DBFA5544B' and bd.ep_code='3E7214CA129E461DBB17242DBFA5544B'
  )d WHERE rid =1),
zd as 
  (SELECT * FROM
  (SELECT bd.id,b.list_no,b.list_date,b.cp_no,bd.gs_no,bd.price_duty ,ROW_NUMBER() OVER(PARTITION BY cp_no,gs_no ORDER BY bd.price_duty asc) AS rid
  from bu_buy b left join bu_buy_detail bd on b.list_no=bd.list_no
  where b.ep_code='3E7214CA129E461DBB17242DBFA5544B' and bd.ep_code='3E7214CA129E461DBB17242DBFA5544B'
  )d WHERE rid =1)
select zj.gs_no,'进货',zj.cp_no
,zj.list_no,zj.list_date,zj.price_duty
,zg.list_no,zg.list_date,zg.price_duty
,zd.list_no,zd.list_date,zd.price_duty,'3E7214CA129E461DBB17242DBFA5544B'
from zj,zg,zd 
where zg.cp_no=zj.cp_no and zg.cp_no=zd.cp_no
and zg.gs_no=zj.gs_no and zd.gs_no=zj.gs_no;