一条联表更新的SQL语句

[zqfleaf]

SQL Server可以这个写
update tp_prolo set f_firsstock=b.f_quantity

from tp_prolo a , tp_recordrow b

where a.vc_pandianid=b.vc_pandianid and a.vc_locatecode=b.vc_locatecode
and a.vc_productid=b.vc_productid and b.vc_pdrowid='000000000001'

请问DB2里面怎么写？？？

有一种比较笨的方法：
update tp_prolo a set a.f_firststock=
case when
(select b.f_quantity from tp_recordrow b
where a.vc_pandianid=b.vc_pandianid
and a.vc_locatecode=b.vc_locatecode
and a.vc_productid=b.vc_productid and
b.vc_pdrowid='000000000001')
is null
then a.f_firststock
else
(select b.f_quantity from tp_recordrow b
where a.vc_pandianid=b.vc_pandianid
and a.vc_locatecode=b.vc_locatecode
and a.vc_productid=b.vc_productid and
b.vc_pdrowid='000000000001')
end
不知哪位大侠有更简洁的方法?

[zqfleaf]

顶

[sbks]

学习

[tianshisuo]

可以稍简化一下：

update tp_prolo a set a.f_firststock=
coalesce((select b.f_quantity from tp_recordrow b
where a.vc_pandianid=b.vc_pandianid
and a.vc_locatecode=b.vc_locatecode
and a.vc_productid=b.vc_productid and
b.vc_pdrowid='000000000001')
,a.f_firststock)