tab a (col1,col2)
tab b (col1,col3)update a set a.col1=b.col3 from a,b where a.col1=b.col1这句在SQLserver可以执行,在oracle下不行。请教在oracle下面怎么改?100分相送!
tab b (col1,col3)update a set a.col1=b.col3 from a,b where a.col1=b.col1这句在SQLserver可以执行,在oracle下不行。请教在oracle下面怎么改?100分相送!
set
a.col1=(select col3 from a,b where a.col1=b.col1)
where a.col1=b.col1
set
a.col1=(select col3 from a,b where a.col1=b.col1)
where a.col1=b.col1 AND B.COL1 IS NOT NULL
我把你们的语句在sqlplus试了一下,系统提示
ORA-00904: invalid column namewhere a.col1=b.col1
*
set
a.col1=(select col3 from a,b where a.col1=b.col1)
where a.col1=(select a.col1 from a,b where a.col1=b.col1)
--------------------------------------- ---------------------------------------
1 100
2 200SQL> select * from b; COL1 COL3
--------------------------------------- ---------------------------------------
1 900
3 700SQL>
SQL> update a
2 set
3 a.col1=(select col3 from a,b where a.col1=b.col1)
4 where a.col1=(select a.col1 from a,b where a.col1=b.col1)
5 ;1 row updatedSQL> select * from a; COL1 COL2
--------------------------------------- ---------------------------------------
900 100
2 200SQL>
2 set
3 a.col1=(select col3 from a,b where a.col1=b.col1)
4 where a.col1=(select a.col1 from a,b where a.col1=b.col1)
5 ;
改成
update a
2 set
3 a.col1=(select col3 from a,b where a.col1=b.col1)
4 where exists (select col3 from a,b where a.col1=b.col1);
会快一些