这个好象会出现single-row subquery returns more than one row的错误提示
其实最开始我用的就是最上面的方法,但子查询返回了多个值,出现了single-row subquery returns more than one row的提示,因为我的两个表本来就不是一对一的。记得某书在上面的语句的后面好象加了什么条件,结果解决了这个问题,但怎么也想不起来了。
update a set col1=(select col11 from b where ...) where a.col22=...;
如果coll1返回的结果是完全相同的则可以用以下方法:update a set col1=(select coll1 from b where rownum<2) 来强制返回一行.
create table a ( a1 int, a2 char(20) );insert into a values(1,'111111111'); insert into a values(2,'222222222'); insert into a values(3,'333333333'); insert into a values(5,'555555555');create table b ( b1 int, b2 char(20) );insert into b values(1,'aaaaaaaaa'); insert into b values(2,'bbbbbbbbb'); insert into b values(3,'ccccccccc'); insert into b values(6,'eeeeeeeee'); select * from a; A1 A2 ----- ------------ 1 111111111 2 222222222 3 333333333 5 555555555 select * from b; B1 B2 ------ ---------- 1 aaaaaaaaa 2 bbbbbbbbb 3 ccccccccc 6 eeeeeeeee Update b set b2=(select a2 from a where a.a1=b.b1) where b1 in(select a1 from a,b where a.a1=b.b1)select * from b; B1 B2 ----- ----------- 1 111111111 2 222222222 3 333333333 6 eeeeeeeee后面的where in条件一定要有,否则在b表中有而在a表没有的记录 将被置为空
来强制返回一行.
(
a1 int,
a2 char(20)
);insert into a values(1,'111111111');
insert into a values(2,'222222222');
insert into a values(3,'333333333');
insert into a values(5,'555555555');create table b
(
b1 int,
b2 char(20)
);insert into b values(1,'aaaaaaaaa');
insert into b values(2,'bbbbbbbbb');
insert into b values(3,'ccccccccc');
insert into b values(6,'eeeeeeeee');
select * from a; A1 A2
----- ------------
1 111111111
2 222222222
3 333333333
5 555555555 select * from b; B1 B2
------ ----------
1 aaaaaaaaa
2 bbbbbbbbb
3 ccccccccc
6 eeeeeeeee Update b set b2=(select a2 from a where a.a1=b.b1)
where b1 in(select a1 from a,b where a.a1=b.b1)select * from b; B1 B2
----- -----------
1 111111111
2 222222222
3 333333333
6 eeeeeeeee后面的where in条件一定要有,否则在b表中有而在a表没有的记录
将被置为空