update temp_users a set a.BUCRD_AMT = (
select b.BUCRD_AMT from temp_cyc1 b
where a.bank = b.bank and a.dept_code = b.dept_code and a.sta_code = b.sta_code ) where a.bucrd_amt is null
and exists (select 1 from temp_cyc1 c
where a.bank = c.bank and a.dept_code = c.dept_code and a.sta_code = c.sta_code
and c.bucrch_yn = 1 and c.bucrd_amt > '0')报错ORA-01427: 单行子查询返回多个行
其中 a.bucrd_amt is null 的数据有很多条
请问怎么实现把temp_cyc1的bucrd_amt的值批量复制到temp_users的bucrd_amt的值呢?
select b.BUCRD_AMT from temp_cyc1 b
where a.bank = b.bank and a.dept_code = b.dept_code and a.sta_code = b.sta_code ) where a.bucrd_amt is null
and exists (select 1 from temp_cyc1 c
where a.bank = c.bank and a.dept_code = c.dept_code and a.sta_code = c.sta_code
and c.bucrch_yn = 1 and c.bucrd_amt > '0')报错ORA-01427: 单行子查询返回多个行
其中 a.bucrd_amt is null 的数据有很多条
请问怎么实现把temp_cyc1的bucrd_amt的值批量复制到temp_users的bucrd_amt的值呢?
sql报的问题就是在等号右边的子查询取值时取出来多个值,所以出错了。
update temp_users a set a.BUCRD_AMT = (
select b.BUCRD_AMT from temp_cyc1 b
where a.bank = b.bank and a.dept_code = b.dept_code and a.sta_code = b.sta_code
and rownum=1 ) where a.bucrd_amt is null
and exists (select 1 from temp_cyc1 c
where a.bank = c.bank and a.dept_code = c.dept_code and a.sta_code = c.sta_code
and c.bucrch_yn = 1 and c.bucrd_amt > '0')
set a.BUCRD_AMT = b.BUCRD_AMT
from temp_cyc1 b
where a.bank = b.bank and a.dept_code = b.dept_code and a.sta_code = b.sta_code ) and a.bucrd_amt is null
and exists (select 1 from temp_cyc1 c
where a.bank = c.bank and a.dept_code = c.dept_code and a.sta_code = c.sta_code
and c.bucrch_yn = 1 and c.bucrd_amt > '0');
update temp_users a
set a.BUCRD_AMT = b.BUCRD_AMT
from temp_cyc1 b
where a.bank = b.bank and a.dept_code = b.dept_code and a.sta_code = b.sta_code ) and a.bucrd_amt is null
and exists (select 1 from temp_cyc1 c
where a.bank = c.bank and a.dept_code = c.dept_code and a.sta_code = c.sta_code
and c.bucrch_yn = 1 and c.bucrd_amt > '0');