UPDATE A.A3 = A.A3+B.B3 的問題
表A 結構 : A1 , A2 ,A3
表B 結構: B1, B2, B3
其中 A1 ,B1 為PK ,切值相同 就是可以使用A1 = B1 了.
請問用SQL 語句或過程該如何實現如下的功能???
更新A 表的 A3 ,用A.A3 與B.B3之和更新.
3>update A
set A3 = (select A.A3 + B.B3 from B where A.A1 = B.B1) ;
7>update (select a1,a3,b1,b3 from a,b where a1=b1) set a3=a3+b3
(測試沒有成功..不知道怎么搞的.)
參考了下面的:
非常佩服。抱著學習的態度，重寫了一下三樓的，在沒有主鍵的情況，請指教：
update a
set a3=(select a3+b3 from b where a1=b1)
where a1=(select b1 from b where a1=b1)
如下:
update con_eme_on20050309 a set a.con_price=(select a.con_price+(b.annuity-a.annuity)+(b.nojob-a.nojob)
+(b.medicare-a.medicare)+(b.birthfee-a.birthfee)+(b.bruisefee-a.bruisefee) from con_eme_on200404 b
where a.emp_cod=b.emp_cod and a.if_act='1' and a.emp_base!=b.emp_base),
a.emp_base=(select b.emp_base from con_eme_on200404 b where a.emp_cod=b.emp_cod and a.if_act='1' and a.emp_base!=b.emp_base),
a.annuity=(select b.annuity from con_eme_on200404 b where a.emp_cod=b.emp_cod and a.if_act='1' and a.emp_base!=b.emp_base),
a.nojob=(select b.nojob from con_eme_on200404 bwhere a.emp_cod=b.emp_cod and a.if_act='1' and a.emp_base!=b.emp_base),
a.medicare=(select b.medicare from con_eme_on200404 b where a.emp_cod=b.emp_cod and a.if_act='1' and a.emp_base!=b.emp_base),
a.birthfee=(select b.birthfee from con_eme_on200404 b where a.emp_cod=b.emp_cod and a.if_act='1' and a.emp_base!=b.emp_base),
a.bruisefee=(select b.bruisefee from con_eme_on200404 b where a.emp_cod=b.emp_cod and a.if_act='1' and a.emp_base!=b.emp_base),
a.nojobbase=(select b.nojobbase from con_eme_on200404 b where a.emp_cod=b.emp_cod and a.if_act='1' and a.emp_base!=b.emp_base),
a.mediabase=(select b.mediabase from con_eme_on200404 b where a.emp_cod=b.emp_cod and a.if_act='1' and a.emp_base!=b.emp_base),
a.birthbase=(select b.birthbase from con_eme_on200404 b where a.emp_cod=b.emp_cod and a.if_act='1' and a.emp_base!=b.emp_base),
a.bruisebase=(select
b.bruisebase from con_eme_on200404 b where a.emp_cod=b.emp_cod and
a.if_act='1' and a.emp_base!=b.emp_base)where a.emp_cod in(select
b.emp_cod from con_eme_on200404 b
where a.emp_cod=b.emp_cod and a.if_act='1' and a.emp_base!=b.emp_base)
公積金:
update con_eme_on20050309 a set a.con_price=(select a.con_price+(b.accumulation-a.accumulation) from con_eme_on200404 b
where
a.emp_cod=b.emp_cod and a.if_act='1' and
a.emp_base!=b.emp_base),a.accumulation=(select b.accumulation from
con_eme_on200404 b where a.emp_cod=b.emp_cod and a.if_act='1' and
a.emp_base!=b.emp_base),
a.accumulationbase=(select
b.accumulationbase from con_eme_on200404 b where a.emp_cod=b.emp_cod
and a.if_act='1' and
a.emp_base!=b.emp_base),a.accumulationbase1=(select b.accumulationbase1
from con_eme_on200404 b
where a.emp_cod=b.emp_cod and a.if_act='1' and a.emp_base!=b.emp_base)
where
a.emp_cod in(select b.emp_cod from con_eme_on200404 b where
a.emp_cod=b.emp_cod and a.if_act='1' and a.emp_base!=b.emp_base)