当前位置: 代码迷 >> Oracle开发 >> 请问一个关于SUM和UPDATE的有关问题
  详细解决方案

请问一个关于SUM和UPDATE的有关问题

热度:87   发布时间:2016-04-24 07:44:02.0
请教一个关于SUM和UPDATE的问题
update tc_customer
set cust_dec001=
(select sum(score_amount) from tc_friend_accscore_d, rel_friend_account,tc_customer
where tc_friend_accscore_d.owner_id=1
and tc_friend_accscore_d.score_amount>0 
and tc_friend_accscore_d.facc_id=rel_friend_account.facc_id
and rel_friend_account.friend_id=tc_customer.friend_id)
where owner_id=1

我想把tc_friend_accscore_d表里符合条件的记录的score_amount字段进行求和,并更新tc_customer表中也符合记录的CUST_DEC001字段,我这样写不报错,但结果是,所有TC_CUSTOMER表里中的CUST_DEC001字段都变成了tc_friend_accscore_d表里score_amount字段的总和了。
各位前辈帮忙看看

------解决方案--------------------
where 后的条件少了.应该要把
where tc_friend_accscore_d.owner_id=1 
and tc_friend_accscore_d.score_amount> 0
and tc_friend_accscore_d.facc_id=rel_friend_account.facc_id 
and rel_friend_account.friend_id=tc_customer.friend_id) 

再写一遍,缩小范围

你的update语句相当于
update a
set b='b'
where c='1'
你就发现问题了
------解决方案--------------------
什么是sql可以,oracle不可以啊?
tc_customer可以不写的
我只会这样写,在where之后再把条件写一遍
update tc_customer 
set cust_dec003=A.sca from 
(select tc_customer.friend_id,sum(score_amount) as sca
from tc_friend_accscore_d, rel_friend_account
where tc_friend_accscore_d.owner_id=1 
and tc_friend_accscore_d.score_amount> 0
and tc_friend_accscore_d.facc_id=rel_friend_account.facc_id 
and rel_friend_account.friend_id=tc_customer.friend_id 
group by tc_customer.friend_id) as A 
where owner_id=1 

and exists(select 'Z' from tc_friend_accscore_d, rel_friend_account
where tc_friend_accscore_d.owner_id=1 
and tc_friend_accscore_d.score_amount> 0
and tc_friend_accscore_d.facc_id=rel_friend_account.facc_id 
and rel_friend_account.friend_id=tc_customer.friend_id )
这样改是可以的,就是不怎么好
  相关解决方案