update table set table.col=(select table1.col from table1 where table.col=table1.col)
udate table1 a set (col1,col2,...)=(select col1,col2,... from table b where a.col1=b.col1) where ....col1要唯一
第一,因为临时表只是大表的一个子集,所以UPDATE最后的where是必不可少的 update table1 set (field1,field2……) =(select field1,field2…… from table2 where table1.pkfield1=table2.pkfield1) 这样的话无法指定究竟那些行需要被更新第二,(select col1,col2,... from table b where a.col1=b.col1)怎样做到唯一,表a中有许多行,表b中也有许多行,这样连接产生的结果集必然是多行的.事实上ORACLE返回的出错信息也是如此.唉,oracle真麻烦
例如两个vachar关键字PK1,PK2 update tablename set (field1,field2……) =(select field1,field2…… from tabletmpname where 条件)where tablename.PK1||'$'||tablename.PK2=tabletmpname.PK1||'$'||tabletmpname.PK2
这样没法保证(select field1,field2…… from tabletmpname where 条件)中的"条件"返回的记录是唯一啊,只要不唯一,ORACLE就报错
penitent(只取一瓢) 写的有什么错?
你们有没有试过这些语句啊 没有一个行的通的.只要set后面的subquery返回的是多行数据,ORACLE就报错 "ORA-01427: single-row subquery returns more than one row"我最后是写了个过程解决的:procedure zwa_outnet is tempRow zwa_outnettemp%rowtype; cursor cur_outnet is select * from zwa_outnet0212 where caller in ( select a.caller from zwa_outnet0212 a,zwa_outnettemp b where a.caller=b.caller)for update; cursor cur_temp is select * from zwa_outnettemp where caller in ( select b.caller from zwa_outnet0212 a,zwa_outnettemp b where a.caller=b.caller);
begin
open cur_temp; for outnetRow in cur_outnet loop fetch cur_temp into tempRow; update zwa_outnet0212 set duration193=tempRow.duration where current of cur_outnet; end loop; end ;
>>没有一个行的通的.只要set后面的subquery返回的是多行数据,ORACLE就报错你可以在subquery 中加条件 and rownum<=1 这样就肯定返回一行数据了。
table1 where table.col=table1.col)
update table1 set (field1,field2……)
=(select field1,field2…… from table2
where table1.pkfield1=table2.pkfield1)
这样的话无法指定究竟那些行需要被更新第二,(select col1,col2,... from table b where a.col1=b.col1)怎样做到唯一,表a中有许多行,表b中也有许多行,这样连接产生的结果集必然是多行的.事实上ORACLE返回的出错信息也是如此.唉,oracle真麻烦
=(select field1,field2…… from tabletmpname where 条件)where tablename.PK1||'$'||tablename.PK2=tabletmpname.PK1||'$'||tabletmpname.PK2
写的有什么错?
没有一个行的通的.只要set后面的subquery返回的是多行数据,ORACLE就报错
"ORA-01427: single-row subquery returns more than one row"我最后是写了个过程解决的:procedure zwa_outnet
is
tempRow zwa_outnettemp%rowtype;
cursor cur_outnet is
select * from zwa_outnet0212 where caller in
( select a.caller from zwa_outnet0212 a,zwa_outnettemp b
where a.caller=b.caller)for update;
cursor cur_temp is
select * from zwa_outnettemp where caller in
( select b.caller from zwa_outnet0212 a,zwa_outnettemp b
where a.caller=b.caller);
begin
open cur_temp;
for outnetRow in cur_outnet loop
fetch cur_temp into tempRow;
update zwa_outnet0212 set duration193=tempRow.duration
where current of cur_outnet;
end loop;
end ;
这样就肯定返回一行数据了。