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;