关于update语句问题，更新不一致的语句

[bobacer]

BANCS_YYB_BAL 表有500条数据

select c.ACTNO,b.invm_curr_bal from bancs.t_invm_w01 b,BANCS_YYB_BAL c
where b.invm_memb_cust_ac=c.actno and b.INVM_CURRENCY='CNY' and substr(b.INVM_GL_CLASSIFICATION_CODE,13,4) in (select tgt_apcode from tgt_apcode_bancs where tgt_num in ('0019','1002')))
这条语句只有一条记录，
按照道理下面的语句应该只更新一条记录，可却把5000条记录都更新了，不知道为什么
update BANCS_YYB_BAL a set a.bal=(select tb.invm_curr_bal from (select c.ACTNO,b.invm_curr_bal from bancs.t_invm_w01 b,BANCS_YYB_BAL c
where b.invm_memb_cust_ac=c.actno and b.INVM_CURRENCY='CNY' and substr(b.INVM_GL_CLASSIFICATION_CODE,13,4) in (select tgt_apcode from tgt_apcode_bancs where tgt_num in ('0019','1002'))) tb
where tb.actno=a.actno)
下面的语句，始终报错ora-00933，在from的地方，
update BANCS_YYB_BAL a set a.bal=tb.invm_curr_bal
from (select c.ACTNO,b.invm_curr_bal from bancs.t_invm_w01 b,BANCS_YYB_BAL c
where b.invm_memb_cust_ac=c.actno and b.INVM_CURRENCY='CNY' and substr(b.INVM_GL_CLASSIFICATION_CODE,13,4) in (select tgt_apcode from tgt_apcode_bancs where tgt_num in ('0019','1002'))) tb
where tb.actno=a.actno

[dinya2003]

你的where没限制住update的表数据。

[bobacer]

就是不知道怎么限制啊

[xpingping]

更新===>说明5000条都满足where条件




下面那个语句明显错的
select …… from table

[yjytiantang]

update BANCS_YYB_BAL a set a.bal=(select tb.invm_curr_bal from (select c.ACTNO,b.invm_curr_bal from bancs.t_invm_w01 b,BANCS_YYB_BAL c
where b.invm_memb_cust_ac=c.actno and b.INVM_CURRENCY='CNY' and substr(b.INVM_GL_CLASSIFICATION_CODE,13,4) in (select tgt_apcode from tgt_apcode_bancs where tgt_num in ('0019','1002'))) tb
where tb.actno=a.actno）
少了where 條件，更新的當然是全部數據了,外層括號外還應該再加where 條件

[fandajun]

update BANCS_YYB_BAL a
set a.bal = (select tb.invm_curr_bal
from (select c.ACTNO, b.invm_curr_bal
from bancs.t_invm_w01 b, BANCS_YYB_BAL c
where b.invm_memb_cust_ac = c.actno
and b.INVM_CURRENCY = 'CNY'
and substr(b.INVM_GL_CLASSIFICATION_CODE, 13, 4) in
(select tgt_apcode
from tgt_apcode_bancs
where tgt_num in ('0019', '1002'))) tb
where tb.actno = a.actno)
这条语句相当于对整个BANCS_YYB_BAL 进行修改，后面还需要增加where条件

[guanghuiy]

update BANCS_YYB_BAL a
   set a.bal = (select tb.invm_curr_bal
                  from (select c.ACTNO, 
                               b.invm_curr_bal
                          from bancs.t_invm_w01 b, 
                               BANCS_YYB_BAL c
                         where b.invm_memb_cust_ac = c.actno
                           and b.INVM_CURRENCY = 'CNY'
                           and substr(b.INVM_GL_CLASSIFICATION_CODE, 13, 4) in
                               (select tgt_apcode
                                  from tgt_apcode_bancs
                                 where tgt_num in ('0019', '1002'))) tb
                 where tb.actno = a.actno)--这个条件是否会有一个actno对应多个invm_curr_bal？看不到数据，应该是条件没限制住 看看能否在加个其他条件在限制一下？