使用 select a.mcnbxh from hzk a , temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ; 可以检索出正确结果,所以我想是update的格式是不是不对
delete from a where aa=(select aa from b where a.aa=b.aa and a.bb=b.bb);update a set column_name=(select column from b where a.aa=b.aa and a.bb=b.bb);试试看
不好意思,没仔细看;如下:update hzk a set a.mcnbxh = ( select b.newdata from temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata );
--------------------------------------------------------------------------------- shuipipi(水皮皮) update hzk a set a.mcnbxh = ( select b.newdata from hzk a , temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ); ---------------------------------------------------------------------------------ORA-01427: 单行子查询返回多于一个行
update hzk a set a.mcnbxh = ( select b.newdata from temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ); 执行速度很慢,完成后提示更新了34008行但实际需要更新的只有1344行 使用 select a.mcnbxh from hzk a , temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ; 可以检索出正确结果
"ORA-01427: 单行子查询返回多于一个行 " 出现此提示的问题在于所关联的条件不够。
我试着搜索了一下论坛,将语句更改为: update hzk a set a.mcnbxh = ( select b.newdata from temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ) where a.mcnbxh = b.olddata;提示:ORA-00904: 无效列名
update hzk a set a.mcnbxh = ( select b.newdata from temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata and rownum=1)
SQL> update hzk a set a.mcnbxh = (select b.newdata from temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata and rownum=1) ;已更新34008行。需要更新的是1344行
update hzk a set a.mcnbxh = (select b.newdata from hzk a , temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata);
RobinHZ(熊掌): 不对的我刚才试了一下,下面语句是可以的: update hzk a set a.mcnbxh = ( select b.newdata from temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ) where exists(select 1 from temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ); 但大虾们常说不要后面的WHERE 条件,为什么? 还有好的吗?
bzszp(SongZip):不是不是,你解决的是另外的问题另外:还有一种方法(其它论坛的回复): 如果b表有主键的话,a表没有主键, 就可以用高性能的Tom的方法了 like this: update ( select emp.dept_name emp_dept_name, dept.dept_name dept_dept_name from emp, dept where emp.dept_no = dept.dept_no ) set emp_dept_name = dept_dept_name;待会就把这分分了去吧
table a : aa ,bb ,cc ,dd,... table b : aa ,bb ,ca ,da.... delete from a where exists (select * from b where a.aa=b.aa and a.bb=b.bb) update a set a.cc=(select b.ca from b.where a.aa=b.aa and a.bb=b.bb)
需要加where条件,不然就对整个a表更新了
看看还是这样子是对的:update hzk a set a.mcnbxh = ( select b.newdata from temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ) where exists(select 1 from temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ); 或者update ( select emp.dept_name emp_dept_name, dept.dept_name dept_dept_name from emp, dept where emp.dept_no = dept.dept_no ) set emp_dept_name = dept_dept_name;其它的答复好像有问题
select a.mcnbxh from hzk a , temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ;
可以检索出正确结果,所以我想是update的格式是不是不对
set column_name=(select column from b where a.aa=b.aa and a.bb=b.bb);试试看
shuipipi(水皮皮)
update hzk a set a.mcnbxh = ( select b.newdata from hzk a , temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata );
---------------------------------------------------------------------------------ORA-01427: 单行子查询返回多于一个行
执行速度很慢,完成后提示更新了34008行但实际需要更新的只有1344行
使用
select a.mcnbxh from hzk a , temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ;
可以检索出正确结果
出现此提示的问题在于所关联的条件不够。
update hzk a set a.mcnbxh = ( select b.newdata from temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ) where a.mcnbxh = b.olddata;提示:ORA-00904: 无效列名
where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata and rownum=1)
;已更新34008行。需要更新的是1344行
update hzk a set a.mcnbxh = ( select b.newdata from temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata )
where exists(select 1 from temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ); 但大虾们常说不要后面的WHERE 条件,为什么?
还有好的吗?
如果b表有主键的话,a表没有主键, 就可以用高性能的Tom的方法了 like this:
update ( select emp.dept_name emp_dept_name,
dept.dept_name dept_dept_name
from emp, dept
where emp.dept_no = dept.dept_no )
set emp_dept_name = dept_dept_name;待会就把这分分了去吧
table b : aa ,bb ,ca ,da....
delete from a where exists (select * from b where a.aa=b.aa and a.bb=b.bb)
update a set a.cc=(select b.ca from b.where a.aa=b.aa and a.bb=b.bb)
where exists(select 1 from temp_xdata b where b.userid = 1 and b.colname = 'mcxh' and a.mcnbxh = b.olddata ); 或者update ( select emp.dept_name emp_dept_name,
dept.dept_name dept_dept_name
from emp, dept
where emp.dept_no = dept.dept_no )
set emp_dept_name = dept_dept_name;其它的答复好像有问题