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字段的总和了。
各位前辈帮忙看看
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字段的总和了。
各位前辈帮忙看看
解决方案 »
- 插入数据出错 vb.net
- exp imp oracle导出/导入问题
- Relational Algebra(关连式代数),高手请进
- 如何建立定长序列,如001~999,不足三位的用'0'补齐?
- PROC中动态SQL能否使用EXEC SQL EXECUTE szTemp INTO :v1,:v2 USING :vIn1;
- 请教一个超级简单的SQL输出结果
- 关于游标增长的问题。请指点
- 关于DBLINK 的几个问题.
- 求备份表(6千万)方案
- oracle 使用sql 把字符串转换成yyyy-mm-dd
- 将Mysql中的一数据库转到Oracle中,写blob字段时出现的问题。
- rowid删除记录的问题
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中就不行了,NOT PROPETY END
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,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
group by tc_customer.friend_id) as A
where owner_id=1
and A.friend_id=tc_customer.friend_id
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 )
这样改是可以的,就是不怎么好